在mysql中创建具有更改日期的数据透视表

所以我尝试用sql查询创建一个pivot表。目前我正在做这个过程中的床单，但想让它少一点痛苦。

ip    domain      type         date      provider type_of_event number_of_events
999  mail.com    normal      2018-08-31  orange      abuse             12
999  mail1.com    normal     2018-08-30  orange      abuse             11
999  mail1.com    normal     2018-08-29  orange      abuse             13
999  mail.com    normal      2018-08-31  orange      abuse             10

我希望是这样

Domain         2018-08-29  2018-08-30  2018-08-31  Grand Total
mail.com                                 22          22
mail1.com             13        11                   24

我有一个格式化的问题，因为我发现大多数的例子有固定的字段，然后日期总是会改变，但我需要它来显示当前的领域，正在工作。它总是以3天为增量。我需要它也引用类型\u的\u事件作为例子只显示一个类型，但我有多个在这个相同的数据库。
这个问题与其他问题不同，因为我没有多个表来提供这些数据。所有这些数据都在一个表中。当我没有什么可加入的时候，我看不出轴心连接会有什么帮助？
所以用这个

SELECT CONCAT('SELECT
domain,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 3 DAY))
  AS `', CURRENT_DATE() - INTERVAL 3 DAY, '`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 2 DAY))
  AS `', CURRENT_DATE() - INTERVAL 2 DAY, '`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 1 DAY))
  AS `', CURRENT_DATE() - INTERVAL 1 DAY, '`,
SUM(number_of_events) AS `Grand Total`
FROM signal_spam
GROUP BY domain;'
) AS theQuery;

给了我

+---------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
----------------+
| theQuery                                                                                                                                                                                                                                                                                                                                                                                                           
|
+---------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
- 
------------------------------------------------------------------------------ 
------------------------------------------------------------------------------ 
------------------------------------------------------------------------------ 
----------------+
| SELECT
domain,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 3 DAY))
  AS `2018-09-11`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 2 DAY))
  AS `2018-09-12`,
SUM(number_of_events * (date_of_event = CURRENT_DATE() - INTERVAL 1 DAY))
  AS `2018-09-13`,
SUM(number_of_events) AS `Grand Total`
FROM signal_spam
GROUP BY domain; |
+---------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
- 
 ---------------------------------------------------------------------------- 
-- 
----------------------------------------------------------------------------- 
- 
----------------------------------------------------------------------------- 
- 
 ----------------+
1 row in set (0.00 sec)

如果我去掉concat，我得到的行中没有计数，只有总计，也没有列出日期

+------------------------------------+--------------------------------------- 
+---------------------------------------+------------------------------------ 
- 
--+-------------+
| domain                             | ', CURRENT_DATE() - INTERVAL 3 DAY, ' 
| 
', CURRENT_DATE() - INTERVAL 2 DAY, ' | ', CURRENT_DATE() - INTERVAL 1 DAY, ' 
| Grand Total |
+------------------------------------+--------------------------------------- 
+---------------------------------------+------------------------------------ 
- 
--+-------------+
| 0.pool.         |                                     0 
|                                     
0 |                                     0 |         258 |
| 143.com               |                                     0 
|                                     
0 |                                     0 |           2 |

mysql中的动态透视表通常是通过编写一个查询来完成的，该查询随后生成一个sql字符串。

SELECT CONCAT('
  SELECT
    domain,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 3 DAY))
      AS `', CURRENT_DATE() - INTERVAL 3 DAY, '`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 2 DAY))
      AS `', CURRENT_DATE() - INTERVAL 2 DAY, '`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 1 DAY))
      AS `', CURRENT_DATE() - INTERVAL 1 DAY, '`,
    SUM(number_of_events) AS `Grand Total`
  FROM tableName
  GROUP BY domain;'
) AS theQuery;

获取该查询的结果，并将其发送回mysql。
例如，今天运行该查询将生成：

SELECT
    domain,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 3 DAY))
      AS `2018-09-11`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 2 DAY))
      AS `2018-09-12`,
    SUM(number_of_events * (date = CURRENT_DATE() - INTERVAL 1 DAY))
      AS `2018-09-13`,
    SUM(number_of_events) AS `Grand Total`
  FROM signal_spam
  GROUP BY domain;

这里有pivot表查询，由mysql动态生成。现在只需将该查询复制/粘贴回客户端，最终结果如下：

+-----------+------------+------------+------------+-------------+
| domain    | 2018-09-11 | 2018-09-12 | 2018-09-13 | Grand Total |
+-----------+------------+------------+------------+-------------+
| mail.com  |          0 |          0 |         22 |          22 |
| mail1.com |         13 |         11 |          0 |          24 |
+-----------+------------+------------+------------+-------------+

在mysql中创建具有更改日期的数据透视表

1条答案

相关问题

热门标签

最新问答