SQL-汇总数据

发布于 2025-01-17 22:44:23 字数 681 浏览 1 评论 0原文

我有一个看起来像这样的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）

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

原文

I've got a sql query that looks like this:

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

This resulted in the following table where a date appears multiple times. Ex:

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

It should have been:

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

What did do wrong? How come there is no aggregation on label and date level?

With kind regards,

LaZZaNoVa

I've tried writing the code above, but the results were different than expected.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南城追梦 2025-01-24 22:44:23

您还必须按迄今为止的时间戳进行分组。当您按时间戳分组时，您将获得时间组件分组。只是由于您的选择而没有显示。

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)

You have to group by the timestamp to date as well. when you group by the timestamp you're getting the time component grouped. It's just not being displayed because of your select.

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)

回复收藏 0 原文

~没有更多了~