如何从MySQL获取高级统计数据？

Question

嗨,

我有一个非常大的MySQL数据库，其表的结构如下：

在示例中，日期采用unix时间戳格式。因此，它将需要转换为正常的美国日期格式，这只是我的数据库中的一些记录。

ID* REG_TYPE

1%-1251917888%*

*

*1*

4*1251917888*

5*

*

7*

*8*

9*1251917888*

10:1251917888*0

问题是我想要得到每个按日期排序的REG_TYPE的计数。下面的表格显示了我想要得到的东西：

REGTYPE(0)：REGTYPE(1)：REGTYPE(2)：REGTYPE(3)：REGTYPE(4)

xxxx：*，*

xxxx：*

我想在数据库中为每个日期获取这个，就像每个日期的摘要一样。

有人能对此提出可能的解决方案吗？从MYSQL获取数据后，我必须将此输出转换为PHP中的数组。在PHP中不使用循环的原因是数据库太大，会导致超时。

诚挚的问候

Answer 1

您要做的是一个透视操作，SQL语法不直接支持该操作。然而，这并不是太复杂，概念上涉及两个步骤：

1. 将数据“放大”成多列，原始数据集中每行一行。这通常是在CASE的情况下完成的。否则..。结束或偶尔使用函数(如oracle中的decode() )。我将在下面的示例中使用案例，因为它同样适用于大多数RDBMSes
2. 使用GROUP BY和聚合函数(SUM、MIN、MAX等)来将许多行折叠到所需的输出行集合中。

我在这个例子中使用这个数据集：

mysql> select * from foo;
+----+------------+----------+
| id | thedate    | reg_type |
+----+------------+----------+
|  1 | 1251917888 |        0 | 
|  2 | 1251917888 |        1 | 
|  3 | 1251917888 |        1 | 
|  4 | 1251917888 |        0 | 
|  5 | 1251917888 |        2 | 
|  6 | 1251917888 |        3 | 
|  7 | 1251917888 |        2 | 
|  8 | 1251917888 |        4 | 
|  9 | 1251917888 |        0 | 
| 10 | 1251917888 |        0 | 
| 11 | 1251831488 |        1 | 
| 12 | 1251831488 |        2 | 
| 13 | 1251831488 |        2 | 
| 14 | 1251831488 |        1 | 
| 15 | 1251831488 |        3 | 
| 16 | 1251831488 |        4 | 
| 17 | 1251831488 |        3 | 
| 18 | 1251831488 |        5 | 
| 19 | 1251831488 |        1 | 
| 20 | 1251831488 |        1 | 
+----+------------+----------+

第一步是“炸毁”数据集：

select id
     , thedate
     , case when reg_type = 0 then 1 else 0 end as reg_type_0
     , case when reg_type = 1 then 1 else 0 end as reg_type_1
     , case when reg_type = 2 then 1 else 0 end as reg_type_2
     , case when reg_type = 3 then 1 else 0 end as reg_type_3
     , case when reg_type = 4 then 1 else 0 end as reg_type_4
     , case when reg_type = 5 then 1 else 0 end as reg_type_5
  from foo;

这就给出了：

+----+------------+------------+------------+------------+------------+------------+------------+
| id | thedate    | reg_type_0 | reg_type_1 | reg_type_2 | reg_type_3 | reg_type_4 | reg_type_5 |
+----+------------+------------+------------+------------+------------+------------+------------+
|  1 | 1251917888 |          1 |          0 |          0 |          0 |          0 |          0 | 
|  2 | 1251917888 |          0 |          1 |          0 |          0 |          0 |          0 | 
|  3 | 1251917888 |          0 |          1 |          0 |          0 |          0 |          0 | 
|  4 | 1251917888 |          1 |          0 |          0 |          0 |          0 |          0 | 
|  5 | 1251917888 |          0 |          0 |          1 |          0 |          0 |          0 | 
|  6 | 1251917888 |          0 |          0 |          0 |          1 |          0 |          0 | 
|  7 | 1251917888 |          0 |          0 |          1 |          0 |          0 |          0 | 
|  8 | 1251917888 |          0 |          0 |          0 |          0 |          1 |          0 | 
|  9 | 1251917888 |          1 |          0 |          0 |          0 |          0 |          0 | 
| 10 | 1251917888 |          1 |          0 |          0 |          0 |          0 |          0 | 
| 11 | 1251831488 |          0 |          1 |          0 |          0 |          0 |          0 | 
| 12 | 1251831488 |          0 |          0 |          1 |          0 |          0 |          0 | 
| 13 | 1251831488 |          0 |          0 |          1 |          0 |          0 |          0 | 
| 14 | 1251831488 |          0 |          1 |          0 |          0 |          0 |          0 | 
| 15 | 1251831488 |          0 |          0 |          0 |          1 |          0 |          0 | 
| 16 | 1251831488 |          0 |          0 |          0 |          0 |          1 |          0 | 
| 17 | 1251831488 |          0 |          0 |          0 |          1 |          0 |          0 | 
| 18 | 1251831488 |          0 |          0 |          0 |          0 |          0 |          1 | 
| 19 | 1251831488 |          0 |          1 |          0 |          0 |          0 |          0 | 
| 20 | 1251831488 |          0 |          1 |          0 |          0 |          0 |          0 | 
+----+------------+------------+------------+------------+------------+------------+------------+

接下来，我们使用或初始查询作为内联视图(也称为“子查询”)，在每个日期的输出中折叠为一行，并对每个reg_type_*列求和：

select thedate
     , sum(i.reg_type_0) as reg_type_0
     , sum(i.reg_type_1) as reg_type_1
     , sum(i.reg_type_2) as reg_type_2
     , sum(i.reg_type_3) as reg_type_3
     , sum(i.reg_type_4) as reg_type_4
     , sum(i.reg_type_5) as reg_type_5
  from (
         select id
              , thedate
              , case when reg_type = 0 then 1 else 0 end as reg_type_0
              , case when reg_type = 1 then 1 else 0 end as reg_type_1
              , case when reg_type = 2 then 1 else 0 end as reg_type_2
              , case when reg_type = 3 then 1 else 0 end as reg_type_3
              , case when reg_type = 4 then 1 else 0 end as reg_type_4
              , case when reg_type = 5 then 1 else 0 end as reg_type_5
           from foo
       ) i
 group by thedate
 order by thedate asc;

(还要注意，您可以将这两个查询合并为一个查询，尽管为了清楚起见，我在这里将它们分开显示；至少在MySQL中，这似乎会产生一个更简单的执行计划，这通常意味着更快的执行--一如既往地，在真实的数据集上测试您的SQL性能，不要相信我的话！)

这为我们提供了：

+------------+------------+------------+------------+------------+------------+------------+
| thedate    | reg_type_0 | reg_type_1 | reg_type_2 | reg_type_3 | reg_type_4 | reg_type_5 |
+------------+------------+------------+------------+------------+------------+------------+
| 1251831488 |          0 |          4 |          2 |          2 |          1 |          1 | 
| 1251917888 |          4 |          2 |          2 |          1 |          1 |          0 | 
+------------+------------+------------+------------+------------+------------+------------+

这就是想要的结果。您可以使用MySQL函数FROM_UNIXTIME将日期转换为日期，在第2部分查询中执行此操作可能是最有效的(该函数的求值次数较少，group by的比较是在整数上进行的，而不是在日期上进行的--不确定这在MySQL中是否真的有所不同)。

Answer 2

除了有一个类似于您发布的输出(您发布的示例输出是一个数据透视表)之外，您还必须使用COUNT BY语句

例如：

SELECT `DATE`, COUNT(*)
FROM `TABLE_NAME`

GROUP BY `REG_TYPE`

Answer 3

您可以使用如下代码在mysql中模拟数据透视表：

SELECT
 SUM(REG_TYPE = 0) AS reg_type_0,
 SUM(REG_TYPE = 1) AS reg_type_1,
 SUM(REG_TYPE = 2) AS reg_type_2,
 SUM(REG_TYPE = 3) AS reg_type_3,
 SUM(REG_TYPE = 4) AS reg_type_4,
 TO_DAYS(FROM_UNIXTIME(date)) AS day_number
FROM my_table
GROUP BY TO_DAYS(FROM_UNIXTIME(date))

日期操作位可能需要进行一些调整。