我有一个 MySQL 表，其中包含 TENANT_NAME、MAX_CALLS 和 TIME_STAMP 列，并且没有主键 ，根据要求，我们每小时插入数据，并且相同的名称可以重复。
现在我想获取需要添加组名称和求和调用并获取一周内每天的前 2 个条目。
例如：22:49 插入数据
TENANT_NAME,MAX_CALLS,TIME_STAMP RS1, 20, 2022-12-07 22:49:17 RS2, 10, 2022-12-07 22:49:17 RS3, 2, 2022-12-07 22:49:17
下一小时 23:49
RS1, 15, 2022-12-07 23:49:17 RS2, 0, 2022-12-07 23:49:17 RS3, 20, 2022-12-07 23:49:17
这样，我就有了1年的数据
现在我想要每天汇总一周的 2 条记录的名称组
像这样
RS1, 35, MON RS3, 22, MON... so on RS4, 40, SUN RS2, 35, SUN
我尝试了这个查询，我能够对姓名和总呼叫进行分组，并显示 DAYNAME，但我想要一周内每天的前 2 条记录。
select a.TENANT_NAME,SUM(a.MAX_CALLS),DAYNAME(a.TIME_STAMP) from TENANT_LIC_DISTRIBUTION AS a group by a.TENANT_NAME,day(a.TIME_STAMP) order by a.MAX_CALLS,a.TIME_STAMP; RS1, 35, MON RS3, 22, MON RS2, 10, MON RS3, 30, TUE RS2, 20, TUE RS1, 10, TUE.... so on RS1, 20, SUN RS2, 10, SUN RS3, 1, SUN
我想像这样获取
RS1, 35, MON RS3, 22, MON RS3, 30, TUE RS2, 20, TUE.... so on RS1, 20, SUN RS2, 10, SUN
请帮助我
谢谢
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
1
898
收藏
尝试使用窗口函数围绕聚合查询附加行号，然后按行号进行限制。这是一种方法。
WITH rank_tenant AS ( SELECT TENANT_NAME, DAY, CALLS, row_number() OVER ( PARTITION BY TENANT_NAME ORDER BY CALLS DESC ) AS row_num FROM (select TENANT_NAME, DAYNAME(TIME_STAMP) as DAY, SUM(MAX_CALLS) as CALLS from TENANT_LIC_DISTRIBUTION group by TENANT_NAME, DAY) as t1 ) SELECT TENANT_NAME, DAY, CALLS FROM rank_tenant WHERE row_num <= 2;