sql - 如果特定日期的表中没有数据，则返回 0 或 Null 值？

Question

我有一个数据库应用程序，用于存储来自特定类型仪表的一系列读数。

假设单个仪表在特定日期没有读数。然后我在一个日期范围内查询该仪表。该特定日期不会有任何信息，这会在显示的输出中留下空白。

所以我的问题是如何为缺失的日期返回 0 值。例如，当仪表没有 2019-07-02 和 2019-07-03 的数据时，请考虑此代码。我希望获得示例输出中所示的零，而不是显示数据间隙。

select m.sdate,
       n.meter_number,
       COALESCE(CAST(n.meter_number as CHAR, 0) AS number_of_cases,
       CASE  WHEN n.count IS NULL THEN '0' ELSE n.count END count
from   (
  SELECT date(dates) as sdate
  FROM   generate_series(
           CAST('2019-07-01' as TIMESTAMP),
           CAST('2019-07-08' as TIMESTAMP),
           interval '1 day'
         ) AS dates
) m
LEFT JOIN (
  SELECT meter_number,
         date(read_time) as rdate,
         COUNT(*)  FROM meter_data.x
  WHERE  date(read_time) BETWEEN '2019-07-01' and  '2019-07-08'
  and    meter_number in ('92589492')
  GROUP BY meter_number,date(read_time)
)n
ON m.sdate=n.rdate ;

2019-07-01  92589492 96
2019-07-02  92589492  0
2019-07-03  92589492  0
2019-07-04  92589492 96
2019-07-05  92589492 96
2019-07-06  92589492 96
2019-07-07  92589492 96
2019-07-08  92589492 96

Answer 1

我得到了我的解决方案。首先我生成日期系列并与我作为 varchar 传递的 meterno 交叉连接。我使用 regexp_split_to_table 函数按行显示传递的 meterno，然后我与日期交叉连接。

请检查我的解决方案。

select m.sdate , COALESCE (m.meterno,'')meter_number,
CASE  WHEN n.count IS NULL THEN '0' ELSE n.count END count
from 
(select date(d) as sdate, mn.meterno 
from  generate_series (CAST('2019-07-01' as TIMESTAMP), CAST('2019-07-06' as TIMESTAMP),  interval '1 day') d
 cross join ( select regexp_split_to_table( '92590714_92590130_92589492_92590381', '_') as meterno) mn)m 
 LEFT JOIN
(SELECT meter_number,date(read_time) as rdate,COUNT(*)  FROM meter_data.load_survey
WHERE date(read_time) BETWEEN '2019-07-01' and  '2019-07-06' and meter_number in ('92590714','92590130','92589492','92590381')  
GROUP BY meter_number,date(read_time) )n
ON m.sdate=n.rdate and m.meterno=n.meter_number
GROUP BY m.sdate,m.meterno,n.count