mysql - 如何使用同一列中的数据创建 SQL View 作为单独的行？

Question

我有一个表，将多个不同的指标保存在同一个表中。有一个“值” 列用于度量的值，另一列用于标识度量的实际含义。然后，我有一个纪元时间戳列、一个网站列和一个主键 ID 列。

我正在尝试创建一个 MySQL View 以将每个指标显示为单独的列，并让每一行代表每天每个指标的最新值(一天中一个指标可能有多个行，我想然后仅显示当天最晚时间的值)。

编辑:值列只是给定时间的总和，因此不应将这些值中的任何一个相加/相加。对于 page_views，该值将是从时间开始到现在的总和。

当前表

+----+---------------+--------------+-----------------+-------+
| ID | TIMESTAMP     | WEBSITE      | METRIC_TYPE     | VALUE |
+----+---------------+--------------+-----------------+-------+
| 1  | 1546610400000 | example1.com | page_views      | 101   |
| 2  | 1546610401000 | example1.com | page_views      | 117   |
| 3  | 1546610400000 | example1.com | unique_visitors | 30    |
| 4  | 1546610401000 | example2.com | page_views      | 22    |
| 5  | 1546610401000 | example2.com | ad_referrals    | 8     |
| 6  | 1546681000300 | example1.com | page_views      | 206   |
+----+---------------+--------------+-----------------+-------+

注意上面的片段:所有时间戳都是 01/04/2019，除了第 6 行。我想创建一个 SQL 查询来将该表转换为以下 View :

期望的 View

+------------+--------------+------------+-----------------+--------------+
| DATE       | WEBSITE      | PAGE_VIEWS | UNIQUE_VISITORS | AD_REFERRALS |
+------------+--------------+------------+-----------------+--------------+
| 01/04/2019 | example1.com | 117        | 30              | NULL         |
| 01/04/2019 | example2.com | 22         | NULL            | 8            |
| 1/05/2019  | example1.com | 206        | NULL            | NULL         |
+------------+--------------+------------+-----------------+--------------+

我知道如何使用 DATE_FORMAT(FROM_UNIXTIME(floor(timestamp/1000)) 和 TIME(FROM_UNIXTIME(floor(timestamp/1000))) 将时间戳转换为日期。

我需要的是对这些结果进行透视，并且每天只为每个指标选择最新的记录。

我已经多次尝试重新加入表格，但与我正在寻找的结果相去甚远(情况是 secret 数据，因此该数据用于不同的指标，但格式相同)。

Answer 1

你可以使用条件聚合，但是你需要过滤到每天的最后一行:

select date(FROM_UNIXTIME(floor(timestamp/1000)) as date, website,
       max(case when metric_type = 'page_views' then value end) as page_views,
       max(case when metric_type = 'unique_visitors' then value end) as unique_visitors,
       max(case when metric_type = 'ad_referrals' then value end) as ad_referrals
from t
where t.timestamp = (select max(t2.timestamp)
                     from t t2
                     where date(FROM_UNIXTIME(floor(t2.timestamp/1000)) = date(FROM_UNIXTIME(floor(t.timestamp/1000)) and
                           t2.website = t.website and
                           t2.metric_type = t.metric_type
                    )
group by date, website;