mysql - 如何检索时间范围聚合上的所有列？

Question

我目前正在努力研究如何在其他时间聚合(周、月、季度等)中聚合我的每日数据。

这是我的原始数据类型的样子:

| date     | traffic_type | visits  |
|----------|--------------|---------|
| 20180101 | 1            | 1221650 |
| 20180101 | 2            | 411424  |
| 20180101 | 4            | 108407  |
| 20180101 | 5            | 298117  |
| 20180101 | 6            | 26806   |
| 20180101 | 7            | 12033   |
| 20180101 | 8            | 80368   |
| 20180101 | 9            | 69544   |
| 20180101 | 10           | 39919   |
| 20180101 | 11           | 26291   |
| 20180102 | 1            | 1218490 |
| 20180102 | 2            | 410965  |
| 20180102 | 4            | 108037  |
| 20180102 | 5            | 297727  |
| 20180102 | 6            | 26719   |
| 20180102 | 7            | 12019   |
| 20180102 | 8            | 80074   |

首先，我想检查访问总和，无论traffic_type如何:

SELECT date, SUM(visits) as visits_per_day
FROM visits_tbl
GROUP BY date

结果如下:

|    ymd   | visits_per_day |
|:--------:|:--------------:|
| 20180101 |     2294563    |
| 20180102 |     2289145    |
| 20180103 |     2300367    |
| 20180104 |     2310256    |
| 20180105 |     2368098    |
| 20180106 |     2372257    |
| 20180107 |     2373863    |
| 20180108 |     2364236    |

但是，如果我想检查每次聚合的 visits_per_day 最高的具体日期(例如:月)，我很难检索正确的输出。

这是我所做的:

SELECT 
   (date div 100) as y_month, MAX(visits_per_day) as max_visit_per_day
FROM
    (SELECT date, SUM(visits) as visits_per_day
    FROM visits_tbl
    GROUP BY date) as t1
GROUP BY
   y_month

这是我的查询的输出:

| y_month | max_visit_per_day |
|:-------:|:-----------------:|
|  201801 |      2435845      |
|  201802 |      2519000      |
|  201803 |      2528097      |
|  201804 |      2550645      |

但是，我无法知道 attempts_per_day 最高的具体日期是哪一天。

所需输出:

| y_month | max_visit_per_day |    ymd   |
|:-------:|:-----------------:|:--------:|
|  201801 |      2435845      | 20180130 |
|  201802 |      2519000      | 20180220 |
|  201803 |      2528097      | 20180325 |
|  201804 |      2550645      | 20180406 |

ymd 表示 attempts_per_day 最高的那一天。该逻辑将在编程的帮助下在仪表板中使用，以便自动选择时间聚合。有人可以帮我吗？

Answer 1

这是结构化查询语言的结构化部分的工作。也就是说，您将编写一些子查询并将它们视为表。

您已经知道如何查找每天的访问次数。让我们将每天的月份添加到该查询中 ( http://sqlfiddle.com/#!9/a8455e/13/0 )。

                   SELECT date DIV 100 as month, date, 
                          SUM(visits) as visits
                     FROM visits_tbl
                    GROUP BY date

接下来您需要找到每个月的最大每日访问次数。 (http://sqlfiddle.com/#!9/a8455e/12/0)

       SELECT month, MAX(visits) max_daily_visits
         FROM (
                   SELECT date DIV 100 as month, date, 
                          SUM(visits) as visits
                     FROM visits_tbl
                    GROUP BY date
              ) dayvisits
        GROUP BY month

然后，技巧是检索每个月中出现最大值的日期。这需要加入。没有common table expressions (MySQL 缺乏)你需要重复第一个子查询。 (http://sqlfiddle.com/#!9/a8455e/11/0)

SELECT detail.*
  FROM (
           SELECT month, MAX(visits) max_daily_visits
             FROM (
                       SELECT date DIV 100 as month, date, 
                              SUM(visits) as visits
                         FROM visits_tbl
                        GROUP BY date
                  ) dayvisits
            GROUP BY month
        ) maxvisits
   JOIN (
                       SELECT date DIV 100 as month, date, 
                              SUM(visits) as visits
                         FROM visits_tbl
                        GROUP BY date
        ) detail ON detail.visits = maxvisits.max_daily_visits
                AND detail.month = maxvisits.month

这个相当复杂的查询的轮廓有助于解释它。我们将使用一个名为 dayvisits 的虚构表来代替该子查询。

SELECT detail.*
  FROM (
           SELECT month, MAX(visits) max_daily_visits
             FROM dayvisits 
            GROUP BY date DIV 100
        ) maxvisits
   JOIN dayvisits detail ON detail.visits = maxvisits.max_daily_visits
                        AND detail.month = maxvisits.month

您正在子查询中寻找每个月的极值。 (这是一种相当标准的 SQL 操作。)为此，您可以使用 MAX() ... GROUP BY 查询查找该值。然后将其连接到子查询本身以查找与极值相对应的其他值。

如果您确实有公用表表达式，则查询将如下所示。您可以考虑采用名为 MariaDB 的 MySQL 分支，它具有 CTE。

WITH dayvisits AS (
       SELECT date DIV 100 as month, date, 
          SUM(visits) as visits
     FROM visits_tbl
    GROUP BY date
) 
SELECT dayvisits.*
  FROM (
           SELECT month, MAX(visits) max_daily_visits
             FROM dayvisits
            GROUP BY month
        ) maxvisits
   JOIN dayvisits ON dayvisits.visits = maxvisits.max_daily_visits
                AND dayvisits.month = maxvisits.month