mysql按小时分组

vwhgwdsa 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(459)

这是为了显示我的平台在一小时内有多少连接。
在我注解“variable”的地方，它是从前端接收的内容，这个查询将在后端运行以填充图表。

SELECT
    count(server_events.event_type),
    server_events.time_stamp,
    hotspots.partner_id,
    hotspots.partner,
    hotspots.operator_id
FROM
    `msp-data`.server_events
INNER JOIN 
    `adserver`.hotspots, `adserver`.operator
WHERE 
    server_events.time_stamp between "1591930800" and "1592017199" -- variable
    and server_events.event_type = "auth_final"
    and server_events.nas_id = adserver.hotspots.code
    and hotspots.partner_id = "1" -- variable
    and hotspots.operator_id = "2" -- variable
GROUP BY
    server_events.time_stamp div 3600
ORDER BY
    server_events.time_stamp

这是我当前的输出，我发布了完整的查询，但是在这个输出中我没有得到partner和partner\u id或operator\u id的过滤器

count time_stamp partner_id operator_id
6   1591944931  1   1
12  1591945711  1   5
6   1591952103  1   1
36  1591952621  1   1
18  1591956063  1   1
12  1591962118  1   4
6   1591966538  1   1
6   1591968554  1   1
12  1591973267  1   5
18  1591976918  1   1
18  1591978620  1   5
12  1591983139  1   5
12  1591984830  1   1
24  1591989873  1   1
12  1591993080  1   1
30  1591995612  1   1

输出
预期产出为

10 1591930800-1591934399 
15 1591934400-1591937999

sql mysql mysql-workbench epoch back

来源：https://stackoverflow.com/questions/62362428/mysql-group-by-hour-epoch-time

2条答案

按热度按时间

6qqygrtg1#

尝试在下面执行。试着考虑一下草莓的建议。

SELECT SUM(CNT), TM FROM(SELECT DISTINCT
    count(server_events.event_type) OVER(PARTITION BY hotspots.partner_id,hotspots.partner,hotspots.operator_id,server_events.time_stamp/3600) CNT
    server_events.time_stamp/3600 TM
FROM
    `msp-data`.server_events
INNER JOIN 
    `adserver`.hotspots, `adserver`.operator
WHERE 
    server_events.time_stamp between "1591930800" and "1592017199" -- variable
    and server_events.event_type = "auth_final"
    and server_events.nas_id = adserver.hotspots.code
    and hotspots.partner_id = "1" -- variable
    and hotspots.operator_id = "2" -- variable
)    
GROUP BY TM
ORDER BY
    2

mysql版本12下

SELECT SUM(CNT), TM FROM(SELECT 
    count(A.event_type) cnt,
hotspots.partner_id,hotspots.partner,hotspots.operator_id,tm
FROM
    (SELECT A.time_stamp/3600 AS TM,A.* FROM `msp-data`.server_events A ) A
INNER JOIN 
    `adserver`.hotspots, `adserver`.operator
WHERE 
    A.time_stamp between "1591930800" and "1592017199" -- variable
    and A.event_type = "auth_final"
    and A.nas_id = adserver.hotspots.code
    and hotspots.partner_id = "1" -- variable
    and hotspots.operator_id = "2" -- variable
    group by hotspots.partner_id,hotspots.partner,hotspots.operator_id,TM
)     aliaz
GROUP BY TM
ORDER BY
    2

赞(0）回复(0）举报 2021-08-09

ohtdti5x2#

@伊斯梅特古泽尔根

SELECT SUM(CNT), TM FROM(SELECT 
    count(A.event_type) cnt,
    hotspots.partner_id,
    hotspots.partner,
    hotspots.operator_id,
    TM
FROM
    (SELECT 
        A.time_stamp/3600 AS TM,
        A.* FROM `msp-data`.server_events A
    ) A
INNER JOIN 
    `adserver`.hotspots, 
    `adserver`.operator
WHERE 
    A.time_stamp between "1591930800" and "1592017199" -- variable
    and A.event_type = "auth_final"
    and A.nas_id = adserver.hotspots.code
--     and hotspots.partner_id = "1" -- variable
--     and hotspots.operator_id = "2" -- variable
    GROUP BY
        hotspots.partner_id,
        hotspots.partner,
        hotspots.operator_id,
        TM
) B    
GROUP BY TM
ORDER BY
    2

现在提供此输出：

SUM(CNT) TM
6   442206.9253
6   442207.1419
6   442207.9306
6   442208.9175
6   442209.0614
6   442209.2531
6   442209.3533
6   442209.4050
6   442209.4150
6   442209.4969
6   442210.0175
6   442210.1089
6   442210.4747
6   442211.6994
6   442211.7619
6   442212.9272
6   442213.4872
6   442214.0711
6   442214.7964
6   442215.4461
6   442215.5736
6   442215.8106
6   442216.2833
6   442216.2908
6   442216.7419
6   442217.5386
6   442217.6267
6   442218.0083
6   442218.3764
6   442219.4092
6   442219.6553
6   442219.8953
6   442219.9933
6   442220.3000
6   442220.9611
6   442221.0033
6   442221.0297
6   442221.6656
6   442221.7533
6   442221.7897

赞(0）回复(0）举报 2021-08-09

我来回答

mysql按小时分组

2条答案

相关问题

热门标签

最新问答