SQL Server 19 SQL查询优化求助：按ID统计24小时内最高1小时浓度

问题描述 #

需要生成一条查询，返回过去24小时内每个ID的以下信息：

• ID本身
• 24小时内总出现次数
• 1小时窗口内出现次数≥3的最高出现浓度值
• 最高浓度对应的时间

统计需按每个ID单独进行。

当前解决方案 #

尝试通过CTE拆分问题，并用带索引的视图缓存24小时数据，但创建索引时无法使用非确定性函数GETDATE()，需手动指定日期。

视图定义 #

CREATE VIEW vwOccurrences
WITH SCHEMABINDING
AS
SELECT id, ReceivedDateTime, COUNT_BIG(*) AS cnt
FROM TestTable
WHERE ReceivedDateTime >= DATEADD(HOUR, -24, convert(datetime, '2022-08-08 22:59:01.137', 101))
GROUP BY id, ReceivedDateTime ;

索引创建 #

CREATE UNIQUE CLUSTERED INDEX IX_vwOccurrences_id_ReceivedDateTime ON vwOccurrences (id, ReceivedDateTime);

查询语句 #

WITH totals AS (
    SELECT id, SUM(cnt) AS total_cnt
    FROM vwOccurrences
    GROUP BY id
),
concentrations AS (
    SELECT o1.id, o1.ReceivedDateTime, SUM(o2.cnt) AS concentration
    FROM vwOccurrences o1
    LEFT JOIN vwOccurrences o2
        ON o1.id = o2.id
        AND o2.ReceivedDateTime BETWEEN DATEADD(MINUTE, -30, o1.ReceivedDateTime) AND DATEADD(MINUTE, 30, o1.ReceivedDateTime)
    GROUP BY o1.id, o1.ReceivedDateTime
),
max_concentrations AS (
    SELECT id, MAX(concentration) AS max_concentration
    FROM concentrations
    GROUP BY id
),
max_concentration_ReceivedDateTime AS (
    SELECT c.id, c.ReceivedDateTime
    FROM concentrations c
    RIGHT JOIN max_concentrations m ON c.id = m.id AND c.concentration = m.max_concentration
),
final_result AS (
    SELECT DISTINCT o.id, t.total_cnt, m.max_concentration, mct.ReceivedDateTime
    FROM vwOccurrences o
    LEFT JOIN totals t ON o.id = t.id
    INNER JOIN max_concentrations m ON o.id = m.id AND m.max_concentration >= 3
    INNER JOIN max_concentration_ReceivedDateTime mct ON m.id = mct.id 
)
SELECT id, total_cnt, max_concentration, ReceivedDateTime
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY max_concentration ASC) AS rn
    FROM final_result
) AS subquery
WHERE rn = 1
ORDER BY id

测试数据生成脚本 #

import pyodbc
from datetime import datetime, timedelta

# Connect to SQL Server
conn = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=127.0.0.1;'
    'DATABASE=TestDB;'
)

# Create a cursor
cursor = conn.cursor()

# Insert values into the table
id = "123456"

# Get current date
current_date = datetime.now()

# Prepare the SQL query
query = '''
    INSERT INTO TestTable (id, ReceivedDateTime)
    VALUES (?, ?)
'''

# Execute the query
for i in range(1, 300):
    cursor.execute(query, (id, current_date + timedelta(seconds=i)))

# Commit the transaction
conn.commit()

# Close the cursor and connection
cursor.close()
conn.close()

性能瓶颈说明 #

数据库24小时内总出现次数通常为2000-3000次，但ID越少查询越慢，原因是浓度计算需要按ID进行密集的时间窗口比对。希望得到进一步修改建议，提升查询速度。

内容的提问来源于stack exchange，提问作者LukasHolst