SparkSQL2.0查詢統計過去一小時內每15分鐘的請求數

我正在嘗試撰寫一個 SQL 查詢來計算過去一小時內每 15 分鐘收到的請求數。我想回傳最后三個 15 分鐘的間隔。

我的資料看起來像：

log_date, time
2022-11-18, 2022-11-18 17:00:00 UTC
2022-11-18, 2022-11-18 17:10:00 UTC
2022-11-18, 2022-11-18 17:20:00 UTC
2022-11-18, 2022-11-18 17:40:00 UTC

到目前為止我的查詢：

select log_date, FLOOR(unix_timestamp(time)) div (15 * 60) as intervals, count(*)
from data_log
where log_date >= date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 1)
and requests_recieved = true
group by log_date, time
order by intervals desc limit 3

我希望它看起來像：

log_date, time, count
2022-11-18, 2022-11-18 17:00:00 UTC, 2
2022-11-18, 2022-11-18 17:15:00 UTC, 1
2022-11-18, 2022-11-18 17:30:00 UTC, 1

更新：我正在使用 SparkSQL 2.3.1

uj5u.com熱心網友回復：

使用函式轉換為time每 15 分鐘一次TIMESTAMPADD。

SELECT * FROM (
  SELECT
    log_date,
    TIMESTAMPADD(
      MINUTE,
      hour(time) * 60   (minute(time) div 15 * 15),
      date(time)) time15m,
    COUNT(*) count
  FROM t1
  GROUP BY log_date, time15m
  ORDER BY time15m DESC
  LIMIT 3
) s
ORDER BY time15m;

uj5u.com熱心網友回復：

這主要是關于日期/時間功能：

1. 轉換log_time為 15 分鐘間隔
   • 使用 date_format() 截斷到最近的小時
   • 使用 date_add() 添加 0 到 3 個 15 分鐘的間隔
2. 按照同樣的思路計算過去一小時的最后15分鐘間隔作為開始間隔。

Step 1. 創建data_log表并準備測驗資料

create table data_log (
    log_date            date,
    log_time            timestamp,
    requests_received   boolean);

insert into data_log
values
('2022-11-18', '2022-11-18 17:55:00', true),
('2022-11-18', '2022-11-18 18:00:00', true),
('2022-11-18', '2022-11-18 18:10:00', true),
('2022-11-18', '2022-11-18 18:20:00', true),
('2022-11-18', '2022-11-18 18:40:00', true),
('2022-11-18', '2022-11-18 18:50:00', true);

Step 2. 查詢過去一小時的日志條目并聚合到 15 分鐘的時間間隔：

with an_hour_ago as (
select date_add(current_timestamp, interval -1 hour)  as an_hour_ago),
interval_an_hour_ago as (
select date_add(date_format(an_hour_ago, '%y-%m-%d %H:00:00'), interval 15 * floor(date_format(an_hour_ago, '%i') / 15) minute) as start_interval
  from an_hour_ago)
select log_date,
       date_add(date_format(log_time,'%y-%m-%d %H:00:00'), interval 15 * floor(date_format(log_time,'%i') / 15) minute ) as log_interval,
       count(*)  as count
  from data_log d,
       interval_an_hour_ago i  
 where d.requests_received = true
   and d.log_time >= i.start_interval
 group by 1, 2;

結果：

log_date  |log_interval       |count|
---------- ------------------- ----- 
2022-11-18|2022-11-18 18:00:00|    2|
2022-11-18|2022-11-18 18:15:00|    1|
2022-11-18|2022-11-18 18:30:00|    1|
2022-11-18|2022-11-18 18:45:00|    1|

uj5u.com熱心網友回復：

此查詢與所有 MySQL 版本兼容。

SET time_zone = 'UTC';
SET @i=0;
SET @t_now = unix_timestamp();  /*Request time*/
SET @hr = 3600 * (@t_now div 3600);  /*Request hour*/

SELECT log_date, from_unixtime(qr * 15 * 60   @hr) AS `time`,  COUNT(qr) AS `count` FROM (
  
  SELECT 
    log_date, 
    @i:=((@t_now - unix_timestamp(`time`)) div 60)  AS m, /* minutes elapsed */
    FLOOR(@i/15) as qr /*hour quarter*/
  FROM data_log WHERE requests_recieved = true     
  
) AS t1
WHERE m <= 60 /* Last hour only */
GROUP BY log_date, qr /* Group by each hour quarter */
ORDER BY log_date, qr
;

我的測驗資料的結果樣本：

日志日期 |時間 |計數
---------- -------------------- -----
2022-11-19 | 2022-11-19 03:30:00 | 3個
2022-11-19 | 2022-11-19 03:45:00 | 4個

標籤：数据库apache-spark-sql聚合间隔

上一篇：如何洗掉磁區中具有特定值的尾隨連續記錄？

下一篇：SQL代碼回傳意外結果。如何糾正