类似子查询除法的简化解决方案
类似子查询除法的简化解决方案
提问于 2021-09-02 01:36:07
我在PostgreSQL 13中有一个表，它看起来如下(为了这个问题而修改)：
SELECT * FROM visits.visitors_log;
visitor_id | day | source
--------------+------------------------+----------
9 | 2019-12-30 12:10:10-05 | Twitter
7 | 2019-12-14 22:10:26-04 | Netflix
5 | 2019-12-13 15:21:04-05 | Netflix
9 | 2019-12-22 23:34:47-05 | Twitter
7 | 2019-12-22 00:10:26-04 | Netflix
9 | 2019-12-22 13:20:42-04 | Twitter在将时间转换到另一个时区之后，我想计算2019年到12-22年来自特定来源的访问量的百分比。
涉及四个步骤：
- 转换时区
- 算一算那天总共拜访了多少次
- 算一算那一天从Netflix源起的总访问量
- 把这两个数字除以得到百分比。
我编写了这段代码，它可以工作，但似乎是重复的，而且不太干净：
SELECT (SELECT COUNT(*) FROM (SELECT visitor_id, source, day AT TIME ZONE 'PST' FROM visits.visitors_log WHERE day::date = '2019-12-22') AS a
WHERE day::date = '2019-12-22' AND source = 'Netflix') * 100.0
/
(SELECT COUNT(*) FROM (SELECT visitor_id, source, day AT TIME ZONE 'PST' FROM visits.visitors_log WHERE day::date = '2019-12-22') AS b
WHERE day::date = '2019-12-22')
AS visitors_percentage;有人能提出一个更清晰的方法来回答这个问题吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-09-02 01:49:39
嗯嗯。。。可以使用窗口函数计算总计：
SELECT source, COUNT(*) / SUM(COUNT(*)) OVER () as visitors_percentage
FROM visits.visitors_log
WHERE (day AT TIME ZONE 'PST')::date = '2019-12-22'
GROUP BY SOURCEStack Overflow用户
发布于 2021-09-02 02:27:49
使用聚合FILTER子句：
SELECT count(*) FILTER (WHERE source = 'Netflix') * 100.0
/ count(*) AS visitors_percentage
FROM visits.visitors_log
WHERE day >= timestamp '2019-12-22' AT TIME ZONE 'PST'
AND day < timestamp '2019-12-23' AT TIME ZONE 'PST';请参见：
我重新定义了WHERE条件，因此它是"sargable“，并且可以在(day)上使用索引。在列上有表达式的谓词不能使用普通索引。因此，我将包含的下界和排他的上限(给定时区的日边界)的计算移到了WHERE子句中表达式的右侧。
对大表的性能有很大的影响。
如果您经常使用该查询，请考虑为其设置一个函数：
CREATE OR REPLACE FUNCTION my_func(_source text, _day date, _tz text)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT round(count(*) FILTER (WHERE source = _source) * 100.0 / count(*), 2) AS visitors_percentage
FROM visits.visitors_log
WHERE day >= _day::timestamp AT TIME ZONE _tz
AND day < (_day + 1)::timestamp AT TIME ZONE _tz;
$func$;呼叫：
SELECT my_func('Netflix', '2019-12-22', 'PST');我加入了round()，这是一个完全可选的添加。
db<>fiddle https://dbfiddle.uk/?rdbms=postgres_13&fiddle=30b5ecfcbdf9c8ed8a86a67cfb9ec81d
旁白："day“是timestamp with time zone列的一个相当具有误导性的名称。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69023066
复制相关文章
相似问题
腾讯云开发者
