Google BigQuery统计月内出现超15天MAC地址的SQL优化咨询

优化后查询语句 #

SELECT
  macAddress,
  COUNT(DISTINCT TIMESTAMP_TRUNC(time, DAY)) AS days
FROM `my_table`
WHERE
  EXTRACT(YEAR FROM time) = 2021
  AND EXTRACT(MONTH FROM time) = 6
GROUP BY macAddress
HAVING days > 15

优化说明 #

• 去掉了多层嵌套子查询：直接使用COUNT(DISTINCT 按天截断的时间)一步统计每个MAC地址当月出现的天数，逻辑简洁性大幅提升
• 删除了不必要的排序操作：原语句最内层子查询的ORDER BY对统计结果没有任何影响，还会额外消耗计算资源，优化后直接移除
• 简化了时间筛选条件：用提取年、月的方式筛选6月数据，避免手动补全月末时分秒容易出现的边界错误，也可以根据使用习惯换成DATE(time) BETWEEN '2021-06-01' AND '2021-06-30'写法，二者性能基本一致
• 删除了最外层多余的GROUP BY：原语句内层已经按MAC地址分组聚合，每个MAC仅对应一条统计记录，最外层重复分组完全没有必要
• 用HAVING替代外层WHERE筛选：聚合结果的筛选直接放在分组后的HAVING子句中，符合SQL标准语法逻辑，代码更易读

内容的提问来源于stack exchange，提问作者Rui Bras Fernandes