SQL实现每日不同Goal Name事件发生次数统计

你的现有SQL是把每个事件的具体时间戳和处理后的Goal名称关联起来，但要实现按日统计每个Goal的发生次数，需要对时间戳做日期截断，再按日期和Goal名称分组统计。

修改后的SQL（分数据库类型） #

1. MySQL/MariaDB

select
    gn.Goal_Name_,
    DATE(gn.EventTimestamp) as EventDate,
    COUNT(*) as DailyCount
from(
    select 
        CASE 
            when substr(GoalName,1,3)='MSD' then 'MSD' 
            when substr(GoalName,1,5)='https' then 'https' 
            else goalname 
        END as Goal_Name_,
        EventTimestamp
    from CG.Goal as goal
)gn
group by gn.Goal_Name_, DATE(gn.EventTimestamp)
order by EventDate, Goal_Name_

2. PostgreSQL

select
    gn.Goal_Name_,
    DATE_TRUNC('day', gn.EventTimestamp)::date as EventDate,
    COUNT(*) as DailyCount
from(
    select 
        CASE 
            when substr(GoalName,1,3)='MSD' then 'MSD' 
            when substr(GoalName,1,5)='https' then 'https' 
            else goalname 
        END as Goal_Name_,
        EventTimestamp
    from CG.Goal as goal
)gn
group by gn.Goal_Name_, DATE_TRUNC('day', gn.EventTimestamp)::date
order by EventDate, Goal_Name_

3. SQL Server

select
    gn.Goal_Name_,
    CAST(gn.EventTimestamp AS DATE) as EventDate,
    COUNT(*) as DailyCount
from(
    select 
        CASE 
            when LEFT(GoalName,3)='MSD' then 'MSD' 
            when LEFT(GoalName,5)='https' then 'https' 
            else goalname 
        END as Goal_Name_,
        EventTimestamp
    from CG.Goal as goal
)gn
group by gn.Goal_Name_, CAST(gn.EventTimestamp AS DATE)
order by EventDate, Goal_Name_

4. Oracle

select
    gn.Goal_Name_,
    TRUNC(gn.EventTimestamp) as EventDate,
    COUNT(*) as DailyCount
from(
    select 
        CASE 
            when SUBSTR(GoalName,1,3)='MSD' then 'MSD' 
            when SUBSTR(GoalName,1,5)='https' then 'https' 
            else goalname 
        END as Goal_Name_,
        EventTimestamp
    from CG.Goal as goal
)gn
group by gn.Goal_Name_, TRUNC(gn.EventTimestamp)
order by EventDate, Goal_Name_

关键修改点 #

• 把原始的EventTimestamp转换成日期格式（去掉时分秒），不同数据库用对应的函数处理
• 分组字段改为Goal_Name_和转换后的日期，不再用原始时间戳
• 用COUNT(*)统计每个分组内的事件数量，即当日该Goal的发生次数

预期输出结果 #

内容的提问来源于stack exchange，提问作者WorkInProgress