SQL实现指定日期范围每日活跃工单数统计遇左连接报错求助
报错原因排查
你使用的SQL引擎(典型如BigQuery)对左连接有语法约束:LEFT OUTER JOIN 必须包含至少一个两侧表字段的等值匹配条件,你现有SQL的JOIN条件全是范围判断,没有等值匹配项,因此触发报错。
正确实现方案
我们可以通过CTE拆分统计逻辑,用等值左连接绕开语法限制,同时简化区间重叠判断逻辑,还要额外处理未关闭工单Closed字段为空、以及异常工单(创建时间晚于关闭时间)的情况。
如果需要保留统计范围内所有日期(即使当日活跃工单数为0),可以使用如下完整SQL:
WITH calendar AS ( -- 生成所有需要统计的日期窗口:当日0点~当日22点 SELECT DATE(calendar) AS stat_date, CAST(CONCAT(DATE(calendar), " 00:00:00") AS DATETIME) AS start, CAST(CONCAT(DATE(calendar), " 22:00:00") AS DATETIME) AS ending FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 DAY)) AS calendar ), daily_active AS ( -- 统计每日有活跃的工单数量 SELECT DATE(c.start) AS stat_date, COUNT(DISTINCT m.Ticket) AS active_cnt FROM calendar c CROSS JOIN MINITABLE m WHERE -- 过滤异常工单:创建时间晚于关闭时间的无效数据 m.Created <= IFNULL(m.Closed, CURRENT_DATETIME()) -- 判断工单生命周期与当日窗口有重叠,等价于你原来的4个OR判断 AND m.Created <= c.ending AND IFNULL(m.Closed, CURRENT_DATETIME()) >= c.start GROUP BY stat_date ) -- 用等值左连接关联日历表与活跃统计表,保留所有统计日期 SELECT c.stat_date AS Start, IFNULL(a.active_cnt, 0) AS `Active Tickets` FROM calendar c LEFT JOIN daily_active a USING(stat_date) ORDER BY c.stat_date ASC
如果不需要展示无活跃工单的日期,可直接删除外层的左连接逻辑,使用daily_active的查询结果即可。
内容的提问来源于stack exchange,提问作者Maho Lau
相关产品推荐
相关产品推荐

