我有两张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 ...
但我想知道有没有更好的办法。将数据拼接在一起似乎也是一项挑战。
3条答案
按热度按时间flmtquvp1#
我缺少的是横向连接。我可以通过以下方法实现我想要的:
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 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;
nuypyhwy3#
如果您正在寻找提示,那么以下是一些:
你知道吗
generate_series()
以及如何使用它来组成一个给定开始和结束日期的日期表?如果没有,那么在这个网站上有很多例子。要在任何给定日期回答此问题,您只需为每个应用程序设置两个度量值,其中只有一个度量值用于将一个应用程序与其他应用程序进行比较。您在第1部分中的查询表明您知道这两个度量是什么。
提示1和2应该足以完成这项任务。我唯一能补充的是,你不用担心让数据库做“太多的工作”,这就是它要做的。如果它做得不够快,那么你可以考虑优化,但是在你进入那一步之前,集中精力得到你想要的答案。
请评论,如果你需要进一步澄清这一点。