Динамическое создание сложных сверток в MySQL

Это действительно несколько вопросов в одном, прошу прощения за перегрузку вопросов, но мне просто нужно, чтобы они были выполнены элегантно. Я могу справиться с простыми запросами в MySQL, но эти сложные таблицы обычно меня довольно сильно сбивают с толку, и я еще не знаком с динамическим SQL. Ищете простые решения (но не жестко запрограммированные) :. Я не уверен, что это слишком много, чтобы задать вопрос о SO, если это действительно слишком, пожалуйста, ответьте на одно или два агрегирования и дайте мне инструменты, чтобы я мог сам создавать такие агрегаты.

Мои данные структурированы следующим образом:

+-----------------------------------------------------------------+
|  timestamp           group   url         metric columns here    |
+-----------------------------------------------------------------+
| 2018-05-01 14:30:00 6732    abc.com     -0.3673 -0.0914 4.0183  |
| 2018-05-01 14:30:00 6732    xyz.com      4.2187  0.3407 12.3832 |
| 2018-05-01 14:30:00 6732    pqr.org     -2.3875 -0.4064 5.8743  |
| 2018-05-01 14:30:00 6732    many.com    -4.4194 -1.0665 4.144   |
| 2018-05-01 14:00:00 7174    abc.com     -6.4021 -1.419  4.5117  |
| 2018-05-01 14:00:00 7174    xyz.com     -1.7971 -1.0396 1.7286  |
| 2018-05-01 14:00:00 7174    many.com     0.5276  0.2621 2.013   |
| 2018-05-01 13:30:00 7174    many.com    -0.4941 -0.1098 4.4982  |
| 2018-05-01 13:30:00 7184    diff.com    -0.6783 -0.1384 4.9013  |
| 2018-05-01 13:30:00 7184    sites.com   -0.1293 -0.0246 5.2608  |
| 2018-05-01 13:30:00 7184    here.com    -0.2703 -0.0669 4.0377  |
+-----------------------------------------------------------------+

По сути, для каждой временной метки у нас есть данные из разных групп, и для каждой группы у нас есть URL-адреса, для каждого URL-адреса мы собираем метрики. URL-адреса и группы объявлений имеют отношение "многие ко многим".

Мне приходится извлекать и агрегировать эти данные разными способами в соответствии с требованиями конкретного случая. Обычно я выбираю те показатели, которые мне нужны, и группирую их по одной или нескольким меткам времени, группе и URL. Однако иногда мне нужно увидеть данные / агрегацию внутри группы, но в итоге я выполняю для них отдельный запрос. Например, при агрегировании по времени, которое я вижу в определенном временном окне, определенная метрика упала или выросла, и я хочу копаться в каждом временном окне отдельно, и мне придется повторять это отдельно, потому что в пределах временного окна определенные группы может подниматься и опускаться и копаться в них, чтобы получить стадию URL-адреса, требует отдельного запроса. Мне нужен способ агрегирования на верхних уровнях - метки времени и группы, а также отображение агрегирования на нижних уровнях. Пример:

Что-то вроде этого действительно поможет:

+---------------------+-------------+-------------+------------------+------------------------------+------------------------------+--------------------+--------------------------------+--------------------------------+---------------------+---------------------------------+---------------------------------+
|      timestamp      | aggregate_1 | aggregate_2 | window_top_group | window_top_group_aggregate_1 | window_top_group_aggregate_2 | window_top_group_2 | window_top_group_2_aggregate_1 | window_top_group_2_aggregate_2 | window_loss_group_1 | window_loss_group_1_aggregate_1 | window_loss_group_1_aggregate_2 |
+---------------------+-------------+-------------+------------------+------------------------------+------------------------------+--------------------+--------------------------------+--------------------------------+---------------------+---------------------------------+---------------------------------+
| 2018-05-01 14:30:00 | -0.3673     | -0.0914     |             6732 | -0.3673                      | -0.3673                      |               7174 | -0.3673                        | -0.3673                        |                7184 | -0.3673                         | -0.3673                         |
| 2018-05-01 14:00:00 | 4.2187      | 0.3407      |             6732 | 4.2187                       | 4.2187                       |               7174 | 4.2187                         | 4.2187                         |                7184 | 4.2187                          | 4.2187                          |
| 2018-05-01 13:30:00 | -2.3875     | -0.4064     |             6732 | -2.3875                      | -2.3875                      |               7174 | -2.3875                        | -2.3875                        |                7184 | -2.3875                         | -2.3875                         |
| 2018-05-01 13:00:00 | -4.4194     | -1.0665     |             6732 | -4.4194                      | -4.4194                      |               7174 | -4.4194                        | -4.4194                        |                7184 | -4.4194                         | -4.4194                         |
| 2018-05-01 12:30:00 | -6.4021     | -1.419      |             7174 | -6.4021                      | -6.4021                      |               7184 | -6.4021                        | -6.4021                        |                6732 | -6.4021                         | -6.4021                         |
| 2018-05-01 12:00:00 | -1.7971     | -1.0396     |             7174 | -1.7971                      | -1.7971                      |               7184 | -1.7971                        | -1.7971                        |                6732 | -1.7971                         | -1.7971                         |
| 2018-05-01 11:30:00 | 0.5276      | 0.2621      |             7174 | 0.5276                       | 0.5276                       |               7184 | 0.5276                         | 0.5276                         |                6732 | 0.5276                          | 0.5276                          |
| 2018-05-01 11:00:00 | -0.4941     | -0.1098     |             7174 | -0.4941                      | -0.4941                      |               6732 | -0.4941                        | -0.4941                        |                7184 | -0.4941                         | -0.4941                         |
| 2018-05-01 10:30:00 | -0.6783     | -0.1384     |             7184 | -0.6783                      | -0.6783                      |               6732 | -0.6783                        | -0.6783                        |                7174 | -0.6783                         | -0.6783                         |
| 2018-05-01 10:00:00 | -0.1293     | -0.0246     |             7184 | -0.1293                      | -0.1293                      |               6732 | -0.1293                        | -0.1293                        |                7174 | -0.1293                         | -0.1293                         |
| 2018-05-01 9:30:00  | -0.2703     | -0.0669     |             7184 | -0.2703                      | -0.2703                      |               6732 | -0.2703                        | -0.2703                        |                7174 | -0.2703                         | -0.2703                         |
+---------------------+-------------+-------------+------------------+------------------------------+------------------------------+--------------------+--------------------------------+--------------------------------+---------------------+---------------------------------+---------------------------------+

Может быть, мы могли бы пойти еще на один уровень глубже? и скажем, при агрегировании временных меток получить верхние URL-адреса для верхних групп или, возможно, комбинации верхних URL-адресов группы?

Несколько других агрегатов, которые действительно могут помочь:

1) Назовите определенный диапазон времени, скажем, полный месяц: Сгруппированы по URL-адресам, показывая лучшее / худшее время и значения для всего диапазона, но также усредняют их по времени суток в течение всего месяца и собирают агрегаты там, как показано:

+-----------+-------------+-------------+------------------------------------+-------------------------------------+--------------------------+----------------------------+--------------+----------------------------+----------------+------------------------------+
|    url    | aggregate_1 | aggregate_2 | best performance timestamp overall | worst performance timestamp overall | peak time of average day | trough time of average day | mean_at_peak | standard_deviation_at_peak | mean_at_trough | standard_deviation_at_trough |
+-----------+-------------+-------------+------------------------------------+-------------------------------------+--------------------------+----------------------------+--------------+----------------------------+----------------+------------------------------+
| abc.com   | -0.3673     | -0.3673     | 2018-05-01 14:30:00                | 2018-05-01 14:30:00                 | 2018-05-01 9:30:00       | 2018-05-01 9:30:00         | 0.5276       | 0.5276                     | 0.5276         | 0.5276                       |
| xyz.com   | 4.2187      | 4.2187      | 2018-05-01 14:00:00                | 2018-05-01 14:00:00                 | 2018-05-01 10:00:00      | 2018-05-01 10:00:00        | 0.5276       | 0.5276                     | 0.5276         | 0.5276                       |
| pqr.org   | -2.3875     | -2.3875     | 2018-05-01 13:30:00                | 2018-05-01 13:30:00                 | 2018-05-01 10:30:00      | 2018-05-01 10:30:00        | 4.2187       | 4.2187                     | 4.2187         | 4.2187                       |
| many.com  | -4.4194     | -4.4194     | 2018-05-01 13:00:00                | 2018-05-01 13:00:00                 | 2018-05-01 10:30:00      | 2018-05-01 10:30:00        | 5.449066667  | 5.449066667                | 5.449066667    | 5.449066667                  |
| abc.com   | -6.4021     | -6.4021     | 2018-05-01 12:30:00                | 2018-05-01 10:30:00                 | 2018-05-01 12:00:00      | 2018-05-01 12:00:00        | 4.2187       | 4.2187                     | 4.2187         | 4.2187                       |
| xyz.com   | -1.7971     | -1.7971     | 2018-05-01 12:00:00                | 2018-05-01 12:00:00                 | 2018-05-01 10:30:00      | 2018-05-01 10:30:00        | 0.5276       | 0.5276                     | 0.5276         | 0.5276                       |
| pqr.org   | 0.5276      | 0.5276      | 2018-05-01 11:30:00                | 2018-05-01 10:30:00                 | 2018-05-01 10:30:00      | 2018-05-01 10:30:00        | 7.985716667  | 7.985716667                | 7.985716667    | 7.985716667                  |
| many.com  | -0.4941     | -0.4941     | 2018-05-01 11:00:00                | 2018-05-01 11:00:00                 | 2018-05-01 11:00:00      | 2018-05-01 11:00:00        | 4.2187       | 4.2187                     | 4.2187         | 4.2187                       |
| many.com  | -0.6783     | -0.6783     | 2018-05-01 10:30:00                | 2018-05-01 10:30:00                 | 2018-05-01 9:30:00       | 2018-05-01 9:30:00         | 0.5276       | 0.5276                     | 0.5276         | 0.5276                       |
| sites.com | -0.1293     | -0.1293     | 2018-05-01 10:00:00                | 2018-05-01 10:00:00                 | 2018-05-01 10:30:00      | 2018-05-01 10:30:00        | 9.522366667  | 9.522366667                | 9.522366667    | 9.522366667                  |
| here.com  | -0.2703     | -0.2703     | 2018-05-01 9:30:00                 | 2018-05-01 9:30:00                  | 2018-05-01 10:00:00      | 2018-05-01 10:00:00        | 4.2187       | 4.2187                     | 4.2187         | 4.2187                       |
+-----------+-------------+-------------+------------------------------------+-------------------------------------+--------------------------+----------------------------+--------------+----------------------------+----------------+------------------------------+

2) Либо для списка указанных URL-адресов, либо для того, чтобы сам запрос построил список URL-адресов, например, тех, которые соответствуют шаблону, или 3 лучших с использованием metric_1 из в каждом окне, покажите процентный вклад для предоставленных или желаемых показателей:

+---------------------+----------+-------------------------------+-------------------------------+-------------------------------+----------+-------------------------------+-------------------------------+-------------------------------+
|      timestamp      | metric_1 | contribution_percentage_url_1 | contribution_percentage_url_2 | contribution_percentage_url_3 | metric_2 | contribution_percentage_url_1 | contribution_percentage_url_2 | contribution_percentage_url_3 |
+---------------------+----------+-------------------------------+-------------------------------+-------------------------------+----------+-------------------------------+-------------------------------+-------------------------------+
| 2018-05-01 14:30:00 | -0.3673  |                            33 |                            26 |                            18 | -0.3673  |                            53 |                            30 |                            11 |
| 2018-05-01 14:00:00 | 4.2187   |                            33 |                            29 |                            12 | 4.2187   |                            30 |                            32 |                            20 |
| 2018-05-01 13:30:00 | -2.3875  |                            53 |                            29 |                            17 | -2.3875  |                            37 |                            32 |                            11 |
| 2018-05-01 13:00:00 | -4.4194  |                            39 |                            27 |                            19 | -4.4194  |                            31 |                            34 |                            10 |
| 2018-05-01 10:30:00 | -6.4021  |                            41 |                            25 |                            15 | -6.4021  |                            31 |                            30 |                            16 |
| 2018-05-01 12:00:00 | -1.7971  |                            45 |                            27 |                            12 | -1.7971  |                            32 |                            30 |                            12 |
| 2018-05-01 10:30:00 | 0.5276   |                            50 |                            35 |                            18 | 0.5276   |                            41 |                            25 |                            13 |
| 2018-05-01 11:00:00 | -0.4941  |                            33 |                            33 |                            16 | -0.4941  |                            44 |                            34 |                            13 |
| 2018-05-01 10:30:00 | -0.6783  |                            53 |                            33 |                            18 | -0.6783  |                            54 |                            33 |                            16 |
| 2018-05-01 10:00:00 | -0.1293  |                            38 |                            31 |                            14 | -0.1293  |                            42 |                            31 |                            17 |
| 2018-05-01 9:30:00  | -0.2703  |                            30 |                            35 |                            11 | -0.2703  |                            30 |                            35 |                            16 |
+---------------------+----------+-------------------------------+-------------------------------+-------------------------------+----------+-------------------------------+-------------------------------+-------------------------------+

3) Вращение: для предоставленного списка дат или + - 5 дней от предоставленной даты и определенного ключевого показателя: сравните показатель по дням:

+-------------+---------+-------------+-------------+-------------+-------------+---------+-------------+-------------+-------------+-------------+---------+
| time of day | date-5  |   date-4    |   date-3    |   date-2    |   date-1    |  date   |   date+1    |   date+2    |   date+3    |   date+4    | date+5  |
+-------------+---------+-------------+-------------+-------------+-------------+---------+-------------+-------------+-------------+-------------+---------+
| 14:30:00    | -0.3673 | 0.5276      | 0.5276      | 0.5276      | 0.5276      | -0.3673 | 0.5276      | 0.5276      | 0.5276      | 0.5276      | -0.3673 |
| 14:00:00    | 4.2187  | 0.5276      | 0.5276      | 0.5276      | 0.5276      | 4.2187  | 0.5276      | 0.5276      | 0.5276      | 0.5276      | 4.2187  |
| 13:30:00    | -2.3875 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -2.3875 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -2.3875 |
| 13:00:00    | -4.4194 | 5.449066667 | 5.449066667 | 5.449066667 | 5.449066667 | -4.4194 | 5.449066667 | 5.449066667 | 5.449066667 | 5.449066667 | -4.4194 |
| 12:30:00    | -6.4021 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -6.4021 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -6.4021 |
| 12:00:00    | -1.7971 | 0.5276      | 0.5276      | 0.5276      | 0.5276      | -1.7971 | 0.5276      | 0.5276      | 0.5276      | 0.5276      | -1.7971 |
| 11:30:00    | 0.5276  | 7.985716667 | 7.985716667 | 7.985716667 | 7.985716667 | 0.5276  | 7.985716667 | 7.985716667 | 7.985716667 | 7.985716667 | 0.5276  |
| 11:00:00    | -0.4941 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -0.4941 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -0.4941 |
| 10:30:00    | -0.6783 | 0.5276      | 0.5276      | 0.5276      | 0.5276      | -0.6783 | 0.5276      | 0.5276      | 0.5276      | 0.5276      | -0.6783 |
| 10:00:00    | -0.1293 | 9.522366667 | 9.522366667 | 9.522366667 | 9.522366667 | -0.1293 | 9.522366667 | 9.522366667 | 9.522366667 | 9.522366667 | -0.1293 |
| 9:30:00     | -0.2703 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -0.2703 | 4.2187      | 4.2187      | 4.2187      | 4.2187      | -0.2703 |
+-------------+---------+-------------+-------------+-------------+-------------+---------+-------------+-------------+-------------+-------------+---------+

4) Существует метрика под названием metric_lg, которая обозначает продолжительность жизни или жизненный цикл URL-адреса на основе его количества. Итак, скажем, с указанной даты или первой временной метки группы, вычислить определенные агрегаты показателей на основе их количества, то есть для одного URL-адреса диапазоны будут 1-5, 5-10, 10-20, 20-50. , 50-80, 80-200, 200-1000, 1000-10000, 10000+: давайте назовем их этапами A, B, C, D, E, F, G, H, I. накапливается, т. е. с момента его появления в группе с момента ее создания. Допустим, группа 7184 была запущена в 2018-05-01 10:00:00, а 7174 была запущена в 2018-04-30 12:00:00, тогда для конкретного URL-адреса, появляющегося в обеих группах, будет накоплено значение metric_lg из начало соответствующей группы, т.е. ее этап жизненного цикла в 7184 будет кумуляцией metric_lg с начала 7184, т.е. 2018-05-01 10:00:00, а этап его жизненного цикла в 7174 будет кумуляцией metric_lg из начало 7174 т.е., 2018-04-30 12:00:00.

Таким образом, для предоставленного списка групп может помочь что-то вроде этого: вычислить другую метрическую совокупность на основе этапов жизненного цикла metric_lg и сравнить производительность группы в разбивке по этапам жизненного цикла.

+---------------------+--------------------+---------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
|      timestamp      | A_aggregate_metric | B _aggregate_metric | C_aggregate_metric | D_aggregate_metric | E_aggregate_metric | F_aggregate_metric | G_aggregate_metric | H_aggregate_metric | I_aggregate_metric |
+---------------------+--------------------+---------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| 2018-05-01 14:30:00 | -0.3673            | 0.5276              | 0.5276             | 0.5276             | 0.5276             | -0.3673            | 0.5276             | 0.5276             | 0.5276             |
| 2018-05-01 14:00:00 | 4.2187             | 0.5276              | 0.5276             | 0.5276             | 0.5276             | 4.2187             | 0.5276             | 0.5276             | 0.5276             |
| 2018-05-01 13:30:00 | -2.3875            | 4.2187              | 4.2187             | 4.2187             | 4.2187             | -2.3875            | 4.2187             | 4.2187             | 4.2187             |
| 2018-05-01 13:00:00 | -4.4194            | 5.449066667         | 5.449066667        | 5.449066667        | 5.449066667        | -4.4194            | 5.449066667        | 5.449066667        | 5.449066667        |
| 2018-05-01 10:30:00 | -6.4021            | 4.2187              | 4.2187             | 4.2187             | 4.2187             | -6.4021            | 4.2187             | 4.2187             | 4.2187             |
| 2018-05-01 12:00:00 | -1.7971            | 0.5276              | 0.5276             | 0.5276             | 0.5276             | -1.7971            | 0.5276             | 0.5276             | 0.5276             |
| 2018-05-01 10:30:00 | 0.5276             | 7.985716667         | 7.985716667        | 7.985716667        | 7.985716667        | 0.5276             | 7.985716667        | 7.985716667        | 7.985716667        |
| 2018-05-01 11:00:00 | -0.4941            | 4.2187              | 4.2187             | 4.2187             | 4.2187             | -0.4941            | 4.2187             | 4.2187             | 4.2187             |
| 2018-05-01 10:30:00 | -0.6783            | 0.5276              | 0.5276             | 0.5276             | 0.5276             | -0.6783            | 0.5276             | 0.5276             | 0.5276             |
| 2018-05-01 10:00:00 | -0.1293            | 9.522366667         | 9.522366667        | 9.522366667        | 9.522366667        | -0.1293            | 9.522366667        | 9.522366667        | 9.522366667        |
| 2018-05-01 9:30:00  | -0.2703            | 4.2187              | 4.2187             | 4.2187             | 4.2187             | -0.2703            | 4.2187             | 4.2187             | 4.2187             |
+---------------------+--------------------+---------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+

Если вам нужен контекст данных, предположим, три показателя: metric_1: доход в долларах metric_2: стоимость в долларах metric_lg: количество трафика в тысячах

PS: делать это предпочтительнее MySQL, а не python, потому что часть этого пойдет на создание пользовательских ВИДОВ, чтобы их можно было часто просматривать и проводить дальнейший анализ.

Это много, большое спасибо, правда