如何在我的子选择中删除相关子查询(由于presto限制)

Question

我试图找到在过去~2个月的14天滚动窗口内执行特定操作(p.action中的任何一行)的美国用户的不同计数。

这是查询。我想要一些技巧，告诉我如何重写这个代码以不使用相关子查询，因为Presto不允许使用相关子查询。

SELECT dt,
    (SELECT COUNT(DISTINCT user_id)
     FROM p.action
     WHERE dt BETWEEN q.dt - 13 AND q.dt -- period of 14 days
        AND country = 'US'
     ) AS 14d_rolling_users
FROM p.action q
WHERE dt BETWEEN '2016-08-24' AND '2016-10-24'  
GROUP BY dt
ORDER BY dt ASC

我一直在绞尽脑汁，试图弄清楚如何在不运行60个单独查询(每天一个)的情况下完成这个任务。

任何帮助感激，谢谢!

Answer 1

最好是手动上卷。

这会将表中的每一行变成14行，并带有额外的rollup__ds时间戳。然后，我们按这个新列分组，以创建一个滚动的14天窗口。它的复杂度是O(N*14) = O(N)，因此是线性的。

SELECT 
  rollup__ds,
  COUNT(DISTINCT username)
FROM (
  SELECT
    username,
    ds
  FROM
    actions
  WHERE 
    ds BETWEEN '2016-08-24' AND '2016-10-24'
    AND country = 'US'
)
CROSS JOIN 
  UNNEST(ARRAY[
    DATE_ADD('day', 0, CAST(ds AS DATE)),
    DATE_ADD('day', 1, CAST(ds AS DATE)),
    ...
    DATE_ADD('day', 12, CAST(ds AS DATE)),
    DATE_ADD('day', 13, CAST(ds AS DATE))
  ]) AS t (rollup__ds)
GROUP BY 
  rollup__ds
ORDER BY 
  rollup__ds
;

希望有帮助!

---

NB - 如果你不需要不同计数最好使用窗口函数，唉，这对不同计数不起作用，因为它们不能像那样求和。

SELECT 
  ds, 
  -- BEWARE this count is NOT distinct!
  SUM(COUNT(username)) over (ORDER BY ds ROWS BEWTEEN 13 PRECEDING AND CURRENT ROW) 
FROM
  actions
WHERE
  ds BETWEEN '2016-08-24' AND '2016-10-24'
  AND country = 'US'
;

Answer 2

没有presto的经验，但从逻辑上讲，您可以重写您的查询，以执行具有2个月日期约束的p.action与具有相同2个月日期约束的p.action的笛卡尔乘积连接(无条件连接)，然后您将不需要内部查询。

SELECT dt,COUNT(DISTINCT user_id)
FROM p.action q1,p.action q2  
WHERE q1.dt BETWEEN '2016-08-24' AND '2016-10-24'  
  AND q2.dt BETWEEN '2016-08-24' AND '2016-10-24' 
  AND q1.dt BETWEEN q2.dt - 13 AND q2.dt -- period of 14 days
  AND country = 'US'
GROUP BY q1.dt
ORDER BY q1.dt ASC

提前创建2个月的表会更有效——这里的条件是在连接之后应用的。