使用presto查询配置单元表时，如果没有列的数据，如何返回该列的值？

我有一个包含以下数据的配置单元表（mytable）：

-----------------------------------------------------------
| date       | device    | hits     | type                |
-----------------------------------------------------------
| 2018-08-15 | device1   | 162684   | messages-total-hits |
| 2018-08-15 | device2   | 70689941 | messages-total-hits |
| 2018-08-15 | device3   | 58979363 | messages-total-hits |
| 2018-08-15 | device4   | 125021   | messages-total-hits |
| 2018-08-15 | device5   | 78750    | messages-total-hits |
| 2018-08-15 | device6   | 2595244  | messages-total-hits |
| 2018-08-16 | device1   | 73140    | activity-total-hits |
| 2018-08-16 | device4   | 19       | activity-total-hits |
| 2018-08-16 | device5   | 75572    | activity-total-hits |
| 2018-08-16 | device6   | 2024704  | activity-total-hits |
-----------------------------------------------------------

我需要获取特定时段内每个设备每天的总点击量，并使用以下查询来完成此操作：

SELECT
date_column,b.device,coalesce(sum(b.hits),0) as total
FROM
(SELECT
CAST(date_column AS DATE) date_column
FROM
(VALUES
   (SEQUENCE(FROM_ISO8601_DATE('2018-08-14'),
             FROM_ISO8601_DATE('2018-08-18'),
             INTERVAL '1' DAY)
   )
) AS t1(date_array)
CROSS JOIN
UNNEST(date_array) AS t2(date_column)
) as a
LEFT JOIN
(SELECT date,device,hits
FROM
mytable
WHERE
date BETWEEN date('2018-08-14') AND date('2018-08-18')
) as b
ON a.date_column = b.date
LEFT JOIN
(SELECT distinct(device) FROM mytable) as c
on b.device = c.device
WHERE
date_column BETWEEN date('2018-08-14') AND date('2018-08-18')
GROUP BY
date_column,
c.device,
b.device
ORDER BY
date_column,
device
;

此查询生成以下结果：

------------------------------------
| date_column | device  | total    |
------------------------------------
| 2018-08-14  | null    | 0        |
| 2018-08-15  | device1 | 162684   |
| 2018-08-15  | device2 | 70689941 |
| 2018-08-15  | device3 | 58979363 |
| 2018-08-15  | device4 | 125021   |
| 2018-08-15  | device5 | 78750    |
| 2018-08-15  | device6 | 2595244  |
| 2018-08-16  | device1 | 73140    |
| 2018-08-16  | device4 | 19       |
| 2018-08-16  | device5 | 75572    |
| 2018-08-16  | device6 | 2024704  |
| 2018-08-17  | null    | 0        |
------------------------------------

问题是，我需要显示设备名称，如果某一特定设备的某一天不存在任何数据，则总数为0。我不明白为什么我的查询没有产生我想要的结果，结果如下：

------------------------------------
| date_column | device  | total    |
------------------------------------
| 2018-08-14  | device1 | 0        |
| 2018-08-14  | device2 | 0        |
| 2018-08-14  | device3 | 0        |
| 2018-08-14  | device4 | 0        |
| 2018-08-14  | device5 | 0        |
| 2018-08-14  | device6 | 0        |
| 2018-08-15  | device1 | 162684   |
| 2018-08-15  | device2 | 70689941 |
| 2018-08-15  | device3 | 58979363 |
| 2018-08-15  | device4 | 125021   |
| 2018-08-15  | device5 | 78750    |
| 2018-08-15  | device6 | 2595244  |
| 2018-08-16  | device1 | 73140    |
| 2018-08-16  | device2 | 0        |
| 2018-08-16  | device3 | 0        |
| 2018-08-16  | device4 | 19       |
| 2018-08-16  | device5 | 75572    |
| 2018-08-16  | device6 | 2024704  |
| 2018-08-17  | device1 | 0        |
| 2018-08-17  | device2 | 0        |
| 2018-08-17  | device3 | 0        |
| 2018-08-17  | device4 | 0        |
| 2018-08-17  | device5 | 0        |
| 2018-08-17  | device6 | 0        |
------------------------------------

有人能解释为什么我的查询在给定的一天没有设备数据的情况下，不生成总值为0的设备名吗？

你必须这样 cross join 带日期的不同设备，然后 left join 原来的table。以下查询应返回预期结果。

SELECT
a.date_column,d.device,coalesce(sum(b.hits),0) as total
FROM
(SELECT
 CAST(date_column AS DATE) date_column
 FROM
 (VALUES
   (SEQUENCE(FROM_ISO8601_DATE('2018-08-14'),
             FROM_ISO8601_DATE('2018-08-18'),
             INTERVAL '1' DAY)
   )
 ) AS t1(date_array)
CROSS JOIN UNNEST(date_array) AS t2(date_column)
) as a
CROSS JOIN (SELECT distinct device FROM mytable) as d
LEFT JOIN
(SELECT date,device,hits
 FROM mytable
 WHERE date BETWEEN date('2018-08-14') AND date('2018-08-18')
) as b ON a.date_column = b.date and b.device = d.device
GROUP BY a.date_column,d.device
ORDER BY a.date_column,d.device
;

使用presto查询配置单元表时，如果没有列的数据，如何返回该列的值？

1条答案

相关问题

热门标签

最新问答