如何创建计算列的广告的查询

Question

我需要创建一个查询来显示编辑的平均工作效率，除了他们的第一本书以每天0.01页的精度出版之外。

我很难找到一种方法来获取自上一列DateOfPublication以来的天数，并使用它将其与NoOfPages列相除。

要显示的列包括

EditorName

BookName

计算列AverageProductivity

以下是表及其列

AGENT  AgentID (PK,varchar(11), not null)
       AgentName (varchar(25), not null)

BOOK   BookName (PK, varchar(45), not null)
       Genre (varchar(25), not null)
       DateOfPublication (date, not null)
       NoOfPages (int, not null)
       WriterID (PK, FK,, varchar(11), not null)
       EditorID (FK, varchar(11), not null)

EDITOR EditorID (PK, varchar(11), not null)
       EditorName (varchar(25), not null)
       Mentors_EditorID (FK, varchar(11), null)

WRITER WriterID (PK, varchar(11), not null)
       WriterName (varchar(25), not null)
       AgentID (FK, varchar(11), not null)

样本数据

insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('Valley of Heroes','10','Fiction','2010-01-12',874,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('The Ruler''s Return','11','Fantasy','2012-03-14',765,'22');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('eRobot','11','Fantasy','2011-04-15',264,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('An Uncle''s Letters','12','Fiction','2012-06-12',258,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('Pretty flowers','13','Album','2013-01-31',148,'22');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('A Tale of Lions','12','Fantasy','2012-08-17',301,'21');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('eRobot','13','Sci Fi','2012-10-04',465,'23');

查询现在生成正确的列，记入GMB，但计算的列显示0值。

下面是查询...

select * from (
    select 
    e.EditorName,
    b.BookName,
    round(
        NoOfPages/datediff(
            day, 
            lag(b.DateOfPublication) over(partition by b.EditorID order by b.DateOfPublication),
            DateOfPublication
        ),
        2
    ) AverageProductivity       
from book b
inner join editor e on e.EditorID = b.EditorID 
) x where AverageProductivity is not null

结果...

Melanie eRobot  0
Melanie An Uncle's Letters  0
George  Pretty flowers  0

Answer 1

您可以使用窗口函数lag()来恢复同一编辑器的上一次发布日期。

然后，datediff(day, ...)可以提供上一本书和当前一本书的出版日期之间的差异。

最后，将当前图书的页数除以日期差异，使用round()限制小数位数，就完成了。

对于编辑器的第一本书，lag()返回null，它将在计算中传播，导致计算列也显示null。

select 
    e.EditorName,
    b.BookName,
    round(
        NoOfPages/datediff(
            day, 
            lag(b.DateOfPublication) over(partition by b.EditorID order by b.DateOfPublication),
            DateOfPublication
        ),
        2
    ) AverageProductivity       
from book b
inner join editor e on e.EditorID = b.EditorID 

如果您想跳过与每个编辑器的第一本书对应的记录，则可以对查询进行包装：

select * from (
    -- above query
) x where AverageProductivity is not null