MySQL 实现用户年度登录频次直方图（含零值补全）

本文详解如何在 mysql 中生成每位用户的年度登录次数统计表，确保所有用户在每一年份下均有记录（未登录则显示为 0），适用于构建完整时间维度的用户行为直方图。

本文详解如何在 mysql 中生成每位用户的年度登录次数统计表，确保所有用户在每一年份下均有记录（未登录则显示为 0），适用于构建完整时间维度的用户行为直方图。

在用户行为分析场景中，仅统计「有登录行为」的年份和用户（如 GROUP BY user, YEAR(date)）会导致数据稀疏——缺失的组合无法参与后续可视化或聚合计算。真正健壮的直方图需满足两个关键要求：维度完整性（所有用户 × 所有年份）与数值完备性（空缺计数显式置 0）。MySQL 原生不支持 FULL OUTER JOIN，但可通过 CROSS JOIN + LEFT JOIN 组合优雅解决。

核心思路：先构建全量维度组合，再左连接填充计数

1. 提取独立维度：分别获取表中全部唯一用户（isp 字段）和全部唯一年份（从 fecha_login 提取）；
2. 笛卡尔积生成全量组合：用 CROSS JOIN 构建 (year, isp) 的完整二维网格；
3. 左连接原始数据并聚合：将网格与原表左连接，对每个 (year, isp) 组合统计匹配行数（count(l.isp) 自动将无匹配行计为 0）。

✅ 注意：必须使用 COUNT(l.isp)（而非 COUNT(*)），因为 LEFT JOIN 后未匹配的行中 l.isp 为 NULL，而 COUNT() 忽略 NULL 值，从而自然返回 0；若误用 COUNT(*)，则会将 NULL 行计为 1，导致错误结果。

以下是适配你实际表结构（login_history，字段为 isp 和 fecha_login）的完整 SQL：

SELECT    y.year,   u.isp,   COUNT(l.isp) AS login_count FROM (   SELECT DISTINCT YEAR(fecha_login) AS year    FROM login_history    WHERE fecha_login IS NOT NULL ) y CROSS JOIN (   SELECT DISTINCT isp    FROM login_history    WHERE isp IS NOT NULL AND isp != '' ) u LEFT JOIN login_history l    ON YEAR(l.fecha_login) = y.year    AND l.isp = u.isp GROUP BY y.year, u.isp ORDER BY y.year ASC, login_count DESC;

关键优化与注意事项

• 性能提示：对 fecha_login 和 isp 字段建立联合索引可显著提升子查询与 JOIN 效率：

  CREATE INDEX idx_year_isp ON login_history (fecha_login, isp);

• 空值防护：子查询中添加 WHERE 条件过滤 NULL 或空字符串 isp，避免无效维度污染结果；
• 年份范围控制（可选）：若只需近 3 年数据，可在 y 子查询中加 WHERE YEAR(fecha_login) >= YEAR(CURDATE()) – 2；
• jsON 输出准备：该结果集可直接被 PHP/Python 等后端语言读取，并用 json_encode() 转为标准 JSON 数组，供前端 echarts 或 Chart.js 渲染直方图。

总结

此方案以声明式 SQL 实现了“稠密矩阵”式聚合，无需应用层循环拼接，兼具可读性、可维护性与执行效率。它不仅解决了当前的直方图需求，更提供了一种通用范式：当需要强制补全多维交叉组合中的缺失项时，CROSS JOIN + LEFT JOIN + COUNT() 是 MySQL 中最可靠、最符合关系代数原理的解法。