今天与几周前的总体功能

Question

我正在研究以下 presto/sql 使用内联滤波器查询查询，以并排比较当前日期范围与几周前的数据。

在我的情况下查询当前日期范围为 2017-09-13至 2017-09-14。

到目前为止，我能够得到以下结果，但不幸的是，这不是我想要的。

任何类型的帮助将不胜感激。

 SELECT
        DATE_TRUNC('day',DATE_PARSE(CAST(sample.datep AS VARCHAR),'%Y%m%d')) AS date,
        CAST(SUM(sample.page_views) FILTER (WHERE sample.datep BETWEEN 20170913 AND 20170914) AS DOUBLE) AS page_views,
        CAST(SUM(sample.page_views) FILTER (WHERE sample.datep BETWEEN 20170906 AND 20170907) AS DOUBLE) AS page_views_weeks_ago 
    FROM
        sample 
    WHERE
        (
            datep BETWEEN 20170906 AND 20170914
        ) 
    GROUP BY
        1 
    ORDER BY
        1 ASC 
    LIMIT 50

实际结果：

+------------+------------+----------------------+
|    date    | page_views | page_views_weeks_ago |
+------------+------------+----------------------+
| 2017-09-06 |          0 |              990,929 |
| 2017-09-07 |          0 |              913,802 |
| 2017-09-08 |          0 |                    0 |
| 2017-09-09 |          0 |                    0 |
| 2017-09-10 |          0 |                    0 |
| 2017-09-11 |          0 |                    0 |
| 2017-09-12 |          0 |                    0 |
| 2017-09-13 |  1,507,715 |                    0 |
| 2017-09-14 |     48,625 |                    0 |
+------------+------------+----------------------+

预期结果：

+------------+------------+----------------------+
|    date    | page_views | page_views_weeks_ago |
+------------+------------+----------------------+
| 2017-09-13 |  1,507,715 |              990,929 |
| 2017-09-14 |     48,625 |              913,802 |
+------------+------------+----------------------+

Answer 1

您可以与前一天一起加入表格。简而言之，我认为我们有一个日期字段，以便可以轻松完成日期子结构。

SELECT date,
       SUM(curr.page_views) AS page_views,
       SUM(prev.page_views) AS page_views_weeks_ago 
FROM sample curr
JOIN sample prev ON curr.date - 7 = prev.date
GROUP BY 1 
ORDER BY 1 ASC