MySQL/HiveQL按年月、location_id、type_id统计原表每行对应记录数

解决方案 #

你可以用窗口函数实现需求，MySQL 8.0+、HiveQL都原生支持该语法，不会折叠原表行，可完整保留所有字段同时追加分组统计值：

SELECT 
    DATE_FORMAT(day_dt, '%d/%m/%Y') AS `date`,
    location_id,
    type_id,
    other_id,
    COUNT(1) OVER (
        PARTITION BY YEAR(day_dt), MONTH(day_dt), location_id, type_id
    ) AS `count`
FROM preliminar;

如果使用的是不支持窗口函数的低版本MySQL（如5.x），可以用子查询关联的方式实现：

SELECT 
    DATE_FORMAT(p.day_dt, '%d/%m/%Y') AS `date`,
    p.location_id,
    p.type_id,
    p.other_id,
    cnt.nb_record AS `count`
FROM preliminar p
LEFT JOIN (
    SELECT 
        YEAR(day_dt) AS y,
        MONTH(day_dt) AS m,
        location_id,
        type_id,
        COUNT(1) AS nb_record
    FROM preliminar
    GROUP BY YEAR(day_dt), MONTH(day_dt), location_id, type_id
) cnt ON YEAR(p.day_dt) = cnt.y 
    AND MONTH(p.day_dt) = cnt.m 
    AND p.location_id = cnt.location_id 
    AND p.type_id = cnt.type_id;

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