时间戳

首页 > 解决方案 > 捕获 null 的总和

sql - 捕获 null 的总和

问题描述

如何按日期和公司捕获属性（bit_id）的空值总和

标签： sqlsql-serverhive

解决方案

如果您的意思是您想计算每个日期/公司有多少个值NULL，那么您可以使用CASE包含在 a 中的语句SUM：

declare @dataTable table (
    recordDate date,
    company varchar(100),
    attribute bit
)

insert into @dataTable values
('2021-09-28','CompanyA',null),
('2021-09-28','CompanyA',null),
('2021-09-28','CompanyA',1),
('2021-09-28','CompanyB',0),
('2021-09-28','CompanyB',null),
('2021-09-29','CompanyA',0),
('2021-09-29','CompanyB',1),
('2021-09-29','CompanyA',null),
('2021-09-29','CompanyB',null),
('2021-09-29','CompanyB',null),
('2021-09-29','CompanyB',null)

select
    recordDate,
    company,
    sum(case when attribute is null then 1 else 0 end) as countNULLs
from @dataTable
group by recordDate, company
order by recordDate, company

结果：

/------------------------------------\
| recordDate | company  | countNULLs |
|------------|----------|------------|
| 2021-09-28 | CompanyA |      2     |
| 2021-09-28 | CompanyB |      1     |
| 2021-09-29 | CompanyA |      1     |
| 2021-09-29 | CompanyB |      3     |
\------------------------------------/

推荐阅读