利用sql语句来统计用户登录数据的实践

1 基本数据情况

当需要对用户登录情况进行统计时，SQL是一个非常强大的工具。通过SQL，可以轻松地从数据库中提取和汇总数据，并以适合分析和报告的方式进行呈现。

在这个示例中，我们有一个名为t_login的表，包含了用户登录的记录。字段有id，user，logintime，department，其中logintime是datetime格式，包括了年月日时分秒信息。具体数据如下表。

针对此数据，我们有两种不同的需求：首先是统计每个用户每个月登录的次数，其次是将登录日期按月显示在列上。

2 统计每个用户每个月登录次数

针对这个需求，我们可以按照以下步骤来实现：

1. 使用GROUP BY语句将数据按照部门、用户、年份和月份进行分组。
2. 使用COUNT函数统计每个组合中的登录次数。
3. 最后，按照部门、用户、年份和月份的顺序进行排序。

SELECT
    department,
    `user`,
    DATE_FORMAT(logintime, '%Y-%m') AS `year_month`,
    COUNT(*) AS count
FROM
    t_login
GROUP BY
    department,
    `user`,
    `year_month`
ORDER BY
    department,
    `user`,
    `year_month`;

查询结果展示

3 将日期按月显示在列上

对于第二个需求，我们可以通过将日期作为列名，统计每个用户在每个月的登录次数。这里的查询需要固定年份和月份，并将其转换为列名。

SELECT
    department,
    `user`,
    SUM(CASE WHEN YEAR(logintime) = 2021 AND MONTH(logintime) = 8 THEN 1 ELSE 0 END) AS '2021_8',
    SUM(CASE WHEN YEAR(logintime) = 2021 AND MONTH(logintime) = 9 THEN 1 ELSE 0 END) AS '2021_9',
    -- ...类似的逻辑，补充更多的年份和月份
    SUM(CASE WHEN YEAR(logintime) = 2023 AND MONTH(logintime) = 7 THEN 1 ELSE 0 END) AS '2023_7'
FROM
    t_login
WHERE
    (logintime >= '2021-08-01' AND logintime < '2023-12-01')
GROUP BY
    department,
    `user`
ORDER BY
    department,
    `user`;

查询结果如下

这两个示例SQL语句提供了满足两种需求的查询方法，并且可以用于导出数据并进一步分析或生成报告。

4 总结

本博客简要描述问题，说明针对用户登录情况的统计需求。 解释如何使用GROUP BY和COUNT函数统计每个用户每个月的登录次数，并提供相应的SQL语句。说明如何将日期按月显示在列上，展示每个用户在每个月的登录情况，并给出相应的SQL查询语句。结果展示和导出数据。提供示例查询结果，说明如何将数据导出到Excel或其他工具中进行分析。总结SQL的强大之处，指出其在数据统计和分析中的应用，并展望如何进一步优化和扩展这些查询以适应更多的需求场景。通过这样的博客结构，读者可以了解到在实际场景中使用SQL进行数据统计和分析的方法，并且能够根据不同需求调整查询，满足自身的数据处理要求。