获取自动态系列天数以来具有最高审阅计数的应用程序

wf82jlnq  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(328)

我有两张table, apps 以及 reviews (为便于讨论而简化):

应用程序表

id int

评论表

id          int
review_date date
app_id      int (foreign key that points to apps)

2个问题:

1. 如何编写查询/函数来回答以下问题:

给出了一系列最早的日期 reviews.review_date 最新消息 reviews.review_date (递增一天),对于每个日期, D ,如果应用程序最早的评论是在或晚于 D ?
如果给定明确的日期,我想我知道如何编写查询:

SELECT
  apps.id,
  count(reviews.*)
FROM
  reviews
  INNER JOIN apps ON apps.id = reviews.app_id
group by
  1
having
  min(reviews.review_date) >= '2020-01-01'
  order by 2 desc
limit 10;

但我不知道如何在给定所需的日期序列的情况下动态查询这些信息,并在单个视图中编译所有这些信息。

2. 对这些数据进行建模的最佳方法是什么?

最好能在每个日期的时候都有一份评论以及 app_id . 到现在为止,我在想一些可能看起来像:

... 2020-01-01_app_id | 2020-01-01_review_count | 2020-01-02_app_id | 2020-01-02_review_count ...

但我想知道有没有更好的办法。将数据拼接在一起似乎也是一项挑战。

flmtquvp

flmtquvp1#

我缺少的是横向连接。我可以通过以下方法实现我想要的:

select
  review_windows.review_window_start,
  id,
  review_total,
  earliest_review
from
  (
    select
      date_trunc('day', review_windows.review_windows) :: date as review_window_start
    from
      generate_series(
        (
          SELECT
            min(reviews.review_date)
          FROM
            reviews
        ),
        (
          SELECT
            max(reviews.review_date)
          FROM
            reviews
        ),
        '1 year'
      ) review_windows
    order by
      1 desc
  ) review_windows
  left join lateral (
    SELECT
      apps.id,
      count(reviews.*) as review_total,
      min(reviews.review_date) as earliest_review
    FROM
      reviews
      INNER JOIN apps ON apps.id = reviews.app_id
    where
      reviews.review_date >= review_windows.review_window_start
    group by
      1
    having
      min(reviews.review_date) >= review_windows.review_window_start
    order by
      2 desc,
      3 desc
    limit
      2
  ) apps_most_reviews on true;
0ejtzxu1

0ejtzxu12#

我想这就是你想要的:

postgres 13或更新

WITH cte AS (  -- MATERIALIZED
   SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS total_ct
   FROM   reviews
   GROUP  BY 1
   )
SELECT *
FROM  (
   SELECT generate_series(min(review_date)
                        , max(review_date)
                        , '1 day')::date
   FROM   reviews
   ) d(review_window_start)
LEFT  JOIN LATERAL (
   SELECT total_ct, array_agg(app_id) AS apps
   FROM  (
      SELECT app_id, total_ct
      FROM   cte c
      WHERE  c.earliest_review >= d.review_window_start
      ORDER  BY total_ct DESC
      FETCH  FIRST 1 ROWS WITH TIES  -- new & hot
      ) sub
   GROUP  BY 1
   ) a ON true;
``` `WITH TIES` 便宜一点。在postgres 13中添加(目前为测试版)。请参见:
大于或等于all()且等于max()速度

#### 12岁及以上

WITH cte AS ( -- MATERIALIZED
SELECT app_id, min(review_date) AS earliest_review, count(*)::int AS total_ct
FROM reviews
GROUP BY 1
)
SELECT *
FROM (
SELECT generate_series(min(review_date)
, max(review_date)
, '1 day')::date
FROM reviews
) d(review_window_start)
LEFT JOIN LATERAL (
SELECT total_ct, array_agg(app_id) AS apps
FROM (
SELECT total_ct, app_id
, rank() OVER (ORDER BY total_ct DESC) AS rnk
FROM cte c
WHERE c.earliest_review >= d.review_window_start
) sub
WHERE rnk = 1
GROUP BY 1
) a ON true;

db<>在这里摆弄
同上,但没有 `WITH TIES` .
我们不需要牵扯到table上 `apps` 完全。table `reviews` 有我们需要的所有信息。
cte `cte` 计算每个应用程序的最早评论和当前总计数。cte避免了重复计算。应该能帮上不少忙。
它总是在postgres 12之前具体化,并且应该在postgres 12中自动具体化,因为它在主查询中被多次使用。否则你可以添加关键字 `MATERIALIZED` 在12年级或更高的时候强迫它。请参见:
如何在加入/下推到外部服务器之前强制计算子查询
优化的 `generate_series()` call生成从最早到最新的一系列日期。请参见:
在postgresql中生成两个日期之间的时间序列
在postgres中加入对generate\u系列的计数查询,并将空值检索为“0”
最后是 `LEFT JOIN LATERAL` 你已经发现了。但是由于多个应用程序可以获得最多的评论,所以检索所有的获奖者,可以是0-n个应用程序。该查询将所有每日获奖者聚合到一个数组中,因此我们每行得到一个结果 `review_window_start` . 或者,定义决胜局最多只能获得一个赢家。请参见:
在postgresql中,横向查询和子查询有什么区别?
nuypyhwy

nuypyhwy3#

如果您正在寻找提示,那么以下是一些:
你知道吗 generate_series() 以及如何使用它来组成一个给定开始和结束日期的日期表?如果没有,那么在这个网站上有很多例子。
要在任何给定日期回答此问题,您只需为每个应用程序设置两个度量值,其中只有一个度量值用于将一个应用程序与其他应用程序进行比较。您在第1部分中的查询表明您知道这两个度量是什么。
提示1和2应该足以完成这项任务。我唯一能补充的是,你不用担心让数据库做“太多的工作”,这就是它要做的。如果它做得不够快,那么你可以考虑优化,但是在你进入那一步之前,集中精力得到你想要的答案。
请评论,如果你需要进一步澄清这一点。

相关问题