查找每天事件的发生次数-SQL

Question

我正试图在SQL数据库中设置一些监控。

select
gn.Goal_Name_
,gn.EventTimestamp as   Timestamp
--,Max(EventTimestamp) as   Timestamp
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 1,2      

生成具有以下结构的表：

Goal_Name_	时间戳
MSD	2021年3月5日11:05:20.162
注销	18.01.2022 20:07:29.799
登录	22022年1月23日09:12:16.597
etc	etc

您就快到了。唯一缺少的是将时间戳转换为日期并计算行数。

select
gn.Goal_Name_
,CAST(gn.EventTimestamp AS DATE FORMAT 'YYYY/MM/DD') as eventDay
,Count(*) as   GoalsCount
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 1,2