如何在MySql 5.5.34中获取每周每天的前两个条目

Question

我有一个 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

请帮助我

谢谢

Answer 1

尝试使用窗口函数围绕聚合查询附加行号，然后按行号进行限制。这是一种方法。

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;