SQL -聚合数据

Question

我有一个sql查询，如下所示：

SELECT DISTINCT
     cast (a.event_timestamp  as date) as DATE
    ,a.label 
    ,COUNT (a.event_row_id) as VISITS
    ,COUNT (DISTINCT a.event_row_id) as UNIQUE_VISITORS
FROM all_page_visits_alias_vw a
WHERE CAST (event_timestamp as date) >= '2018-01-01'
GROUP by a.label, a.event_timestamp

这导致下表中日期出现多次。例如：

DATE|LABEL|VISITS|UNIQUE_VISITORS
18-12-2019| A | 1 | 1
18-12-2019| A | 3 | 1
18-12-2019| A | 4 | 1

它应该是：

DATE|LABEL|VISITS|UNIQUE_VISITORS
18-12-2019| A | 8 | 3

做错什么了？为什么标签和日期级别上没有聚合？

向您致以亲切的问候，

LaZZaNoVa

我试着编写上面的代码，但结果与预期不同。

Answer 1

您还必须按时间戳对日期进行分组。当您按时间戳分组时，您将得到时间组件分组。只是因为你的选择而没有显示出来。

SELECT 
     cast (a.event_timestamp  as date) as DATE
    ,a.label 
    ,COUNT (a.event_row_id) as VISITS
    ,COUNT ( DISTINCT a.event_row_id) as UNIQUE_VISITORS
FROM all_page_visits_alias_vw a
WHERE CAST (event_timestamp as date) >= '2018-01-01'
GROUP by a.label, cast (a.event_timestamp  as date)