文章/答案/技术大牛

发布

社区首页 >问答首页 >使用大表和多和提高查询速度

问使用大表和多和提高查询速度
EN

Stack Overflow用户

提问于 2017-04-05 02:05:43

回答 1查看 61关注 0票数 0

我有一张很大的桌子，看上去像这样：

event_date (DATE), event_cd (TEXT), bat_id (TEXT)

有几百万行。type是作为字符串存储的数字，该字符串表示一种事件类型('14‘、'16’等)。目标是计算给定日期范围和标识符的值。我创建了一个函数，它接受一个标识符、一个开始日期(作为文本)和一个结束日期(作为文本)，并执行以下操作：

CREATE OR REPLACE FUNCTION wOBA_period(player TEXT, start_date TEXT, end_date TEXT)
    RETURNS NUMERIC AS $$
WITH guts AS (SELECT * FROM weights WHERE season = substring(start_date FROM 1 FOR 4) :: INT)
SELECT CASE WHEN sum(CASE WHEN ab_fl = 'T' THEN 1 ELSE 0 END) = 0 THEN NULL ELSE (round(
    ((SELECT wbb FROM guts) * sum(CASE WHEN event_cd = '14' THEN 1 ELSE 0 END)
        + (SELECT whbp FROM guts) * sum(CASE WHEN event_cd = '16' THEN 1 ELSE 0 END)
        + (SELECT w1b FROM guts) * sum(CASE WHEN event_cd = '20' THEN 1 ELSE 0 END)
        + (SELECT w2b FROM guts) * sum(CASE WHEN event_cd = '21' THEN 1 ELSE 0 END)
        + (SELECT w3b FROM guts) * sum(CASE WHEN event_cd = '22' THEN 1 ELSE 0 END)
        + (SELECT whr FROM guts) * sum(CASE WHEN event_cd = '23' THEN 1 ELSE 0 END)) :: NUMERIC
        / (sum(CASE WHEN ab_fl = 'T' THEN 1 ELSE 0 END) + sum(CASE WHEN event_cd = '14' THEN 1 ELSE 0 END)
        + sum(CASE WHEN sf_fl = 'T' THEN 1 ELSE 0 END) + sum(CASE WHEN event_cd = '16' THEN 1 ELSE 0 END)) :: NUMERIC,
    3)) END AS woba
FROM events WHERE bat_id = player AND event_date BETWEEN start_date AND end_date
GROUP BY bat_id;
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

上下文:它根据特定事件计算wOBA (加权基平均)。公式如下：

wOBA = (wBB*BB + wHBP*HBP + w1B*1B + w2B*2B + w3B*3B) / (AB+BB+SF+HBP)

在events表中，event_cd对应于公式中的每个分量('20‘= 1B，'14’= BB，等等)。最后，权重(公式中的wXX)每年都不同，并存储在一个表(weights)中，我为给定的年份提取了该表，并临时将其作为guts提供。

一个问题是这个数字很低。获得wOBA_period函数调用的结果需要8秒。当我试图更新一个需要对每个5000+行进行多个函数调用的不同表时，这就有点麻烦了。

有什么方法可以改进这个功能，使它更快一点吗？

谢谢

样本数据：

来自events

event_date  bat_id      event_cd
2015-05-30  linda001    2
2015-05-30  hellj001    20
2015-05-30  incie001    20
2015-05-30  polla001    2
2015-05-30  goldp001    9
2015-05-30  goldp001    23
2015-05-30  trumm001    21
2015-05-30  hilla001    2
2015-05-30  pachj001    2
2015-05-30  ramia001    2

因此，sum(case when event_cd = '20' then 1 else 0 end) group by bat_id将返回给定bat_id的“20”出现次数。这是wOBA公式中“1B”的数字。

weights表中每年都有这样的一行：

year    wbb     whbp    w1b     w2b     w3b     whr
2016    0.691   0.721   0.878   1.242   1.569   2.015

这些是公式中每个分量的倍数的权重。

wOBA_period函数接受一个bat_id和2个日期，并且应该返回一个数字。

sum

sql

postgresql

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-04-05 02:52:55

当然，我没有您的数据副本，但是您可以尝试以下方法

创建一个视图，尽可能多地处理。
使用一个函数完成这项工作。

我的想法是，函数没有得到很好的优化，特别是当它们动态地创建一个查询时，但是视图可能会有所帮助。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/43220615

复制

相似问题

问使用大表和多和提高查询速度
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用大表和多和提高查询速度EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用大表和多和提高查询速度
EN