为什么我的“计算列”中的数据显示为null / 0值 [英] Why is the Data in my Computed Columns showing up with null / 0 values

问题描述

我需要创建一个查询，以显示从事多于一本书的编辑的平均生产率，除了第一本书的出版精度为每天0.01页。

I need to create a query that shows the average productivity for editors that have worked on more than one book except for their first book published with a precision of 0.01 pages/day.

我现在显示的列正确，但是计算出的列未显示任何计算出的平均值。

I have the correct columns showing now but the computed column is not showing any of the calculated averages.

要显示的列是

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');

这里是查询...

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

推荐答案

SQL Server执行整数除法。因此， 1/2 是 0 ，而不是 0.5 。

SQL Server does integer division. So, 1/2 is 0, not 0.5.

您可以在代码中轻松修复此问题。我认为最简单的方法是：

You can easily fix this in your code. I think the simplest method is:

round(NoOfPages * 1.0 /
      datediff(day, 
               lag(b.DateOfPublication) over(partition by b.EditorID order by b.DateOfPublication),
               DateOfPublication
              ), 2
     )

* 1.0 将其转换为带小数位的数字，从而解决了整数除法问题。

The * 1.0 converts this to a number with decimal places, getting around the integer division problem.

这篇关于为什么我的“计算列”中的数据显示为null / 0值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！