sql - 计算多列中 "empty"行的记录并连接

Question

我在网站上进行了大量搜索，试图找到解决我的问题的方法，我发现了类似的问题，但我还没有找到适合我的情况的解决方案。

我有一个像这样的门票表(它有比这更多的数据):

门票:

+---------+--------------+------------+------------+
| ticketid|  report_date |   impact   |    open    |
+---------+--------------+------------+------------+
|       1 |   29/01/2019 |          1 |       true |
|       2 |   29/01/2019 |          2 |       true |
|       3 |   30/01/2019 |          4 |       true |
|       4 |   27/01/2019 |          1 |       true |
|       5 |   29/01/2019 |          1 |       true |
|       6 |   30/01/2019 |          2 |       true |
+---------+--------------+------------+------------+

还有另一个表保存了上表中影响列的可能值:

影响:

+---------+
| impact  |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
+---------+

我的目标是从工单表中提取结果集，我在其中按 impact、report_date 和 open 标志进行分组并计算每组的票数。因此，对于上面的例子，我想提取如下结果集。

+--------------+------------+------------+-----------+
|  report_date |   impact   |    open    | tkt_count |
+--------------+------------+------------+-----------+
|   27/01/2019 |          1 |       true |         1 |
|   27/01/2019 |          1 |      false |         0 |
|   27/01/2019 |          2 |       true |         0 |
|   27/01/2019 |          2 |      false |         0 |
|   27/01/2019 |          3 |       true |         0 |
|   27/01/2019 |          3 |      false |         0 |
|   27/01/2019 |          4 |       true |         0 |
|   27/01/2019 |          4 |      false |         0 |
|   29/01/2019 |          1 |       true |         2 |
|   29/01/2019 |          1 |      false |         0 |
|   29/01/2019 |          2 |       true |         1 |
|   29/01/2019 |          2 |      false |         0 |
|   29/01/2019 |          3 |       true |         0 |
|   29/01/2019 |          3 |      false |         0 |
|   29/01/2019 |          4 |       true |         0 |
|   29/01/2019 |          4 |      false |         0 |
|   30/01/2019 |          1 |       true |         0 |
|   30/01/2019 |          1 |      false |         0 |
|   30/01/2019 |          2 |       true |         1 |
|   30/01/2019 |          2 |      false |         0 |
|   30/01/2019 |          3 |       true |         0 |
|   30/01/2019 |          3 |      false |         0 |
|   30/01/2019 |          4 |       true |         1 |
|   30/01/2019 |          4 |      false |         0 |
+--------------+------------+------------+-----------+

这看起来很简单，但问题在于“零”行。

对于我在此处展示的示例，在给定的日期范围内，没有影响为 3 的工单或带有打开标志的工单。我无法想出一个查询来显示所有计数，即使某些值没有行也是如此。

谁能帮帮我？

提前致谢。

Answer 1

要解决这类问题，一种方法是生成一个中间结果集，其中包含需要计算值的所有记录，然后将其与原始数据LEFT JOIN，使用聚合。

SELECT
    dt.report_date,
    i.impact,
    op.[open],
    COUNT(t.report_date) tkt_count
FROM 
    (SELECT DISTINCT report_date FROM ticket) dt
    CROSS JOIN impact i
    CROSS JOIN (SELECT 'true' [open] UNION ALL SELECT 'false') op
    LEFT JOIN ticket t 
        ON  t.report_date = dt.report_date
        AND t.impact = i.impact
        AND t.[open] = op.[open]
GROUP BY
    dt.report_date,
    i.impact,
    op.[open]

此查询生成如下中间结果集:

• report_date : 原始数据中所有不同的日期 (report_date)
• impact :包含表 impact
• open :包含 true 或 false 的固定列表(也可以从原始数据中的不同值构建，但值 false 不可用是您的示例数据)

您可以选择更改以上规则，逻辑应该保持不变。例如，如果 report_date 中存在间隔，另一个广泛使用的选项是创建一个日历表。

Demo on DB Fiddle :

report_date         | impact | open  | tkt_count
:------------------ | -----: | :---- | --------:
27/01/2019 00:00:00 |      1 | false |         0
27/01/2019 00:00:00 |      1 | true  |         1
27/01/2019 00:00:00 |      2 | false |         0
27/01/2019 00:00:00 |      2 | true  |         0
27/01/2019 00:00:00 |      3 | false |         0
27/01/2019 00:00:00 |      3 | true  |         0
27/01/2019 00:00:00 |      4 | false |         0
27/01/2019 00:00:00 |      4 | true  |         0
29/01/2019 00:00:00 |      1 | false |         0
29/01/2019 00:00:00 |      1 | true  |         2
29/01/2019 00:00:00 |      2 | false |         0
29/01/2019 00:00:00 |      2 | true  |         1
29/01/2019 00:00:00 |      3 | false |         0
29/01/2019 00:00:00 |      3 | true  |         0
29/01/2019 00:00:00 |      4 | false |         0
29/01/2019 00:00:00 |      4 | true  |         0
30/01/2019 00:00:00 |      1 | false |         0
30/01/2019 00:00:00 |      1 | true  |         0
30/01/2019 00:00:00 |      2 | false |         0
30/01/2019 00:00:00 |      2 | true  |         1
30/01/2019 00:00:00 |      3 | false |         0
30/01/2019 00:00:00 |      3 | true  |         0
30/01/2019 00:00:00 |      4 | false |         0
30/01/2019 00:00:00 |      4 | true  |         1