sql - Postgresql - 引用之前计算的列

Question

我有一些这样的表。

create table programming_language(
id                         smallserial primary key,
name                       text not null,
initial_users              integer not null,
initial_release            date not null
);

create table usage_data(
id                                   smallserial primary key,
language_id                          integer references programming_language(id),
percent_users_change                 decimal(5,2),
increase_or_decrease                 boolean default False, -- false indicates decrease
survey_year                          integer not null
);

现在每个编程语言都有一个初始用户数，如表编程语言的 initial_users 中给定的那样。 usage_data 有 percent_users_change 列，它为我们提供了与前几年相比用户数量增加或减少的百分比。是减少还是增加由 increase_or_column 列给出。现在我需要查询并查找每个 survey_year 中的用户数。我编写了以下查询来实现此目的，但我无法做到，因为您无法在同一个选择查询中引用该变量。

select language_id,case when increase_or_decrease=True then percent_users_change+(lag(previ,0)  over(partition by language_id order by survey_year))
              when increase_or_decrease=False then percent_users_change-(lag(previ,0) over(partition by language_id order by survey_year))
              end as previ
from usage_data;

示例数据:

+----+-------------+----------------------+----------------------+-------------+
| id | language_id | percent_users_change | increase_or_decrease | survey_year |
+----+-------------+----------------------+----------------------+-------------+
|  1 |           1 |                   10 | True                 |        1991 
|  2 |           1 |                  7.5 | True                 |        1993 |
|  3 |           3 |                 12.5 | True                 |        1996 |
|  4 |           4 |                 8.75 | True                 |        1996 |
+----+-------------+----------------------+----------------------+-------------+

例如，对于语言 1，如果初始用户是 10，对于 1991 年，我希望它是 11(上一年的 10+10%)，1992 之后它应该是 11.825(11+11 的 7.5%) ) 等等。

Answer 1

可以从初始用户计算百分比变化

pct_change(i) = init_users * prod_from_0_i(pct_change)

其中 pct_change 表示为 1 +/- percent_users_change/100

例如

# for language 1, init_users = 10 
1991 (change 10%)  -> 10 * (1.10) = 11 
1993 (change 7.5%) -> 10 * (1.10)(1.075) = 11.825  

为此我们需要一个 Product 聚合函数，它没有在 SQL 中实现，但是，我们可以实现它取对数，我们可以应用 SUM 聚合函数。

这是因为以下身份:

log(xy) = log(x) + log(y)

将其推广到一系列 xs

log(xs) = log(x0) + log(x1) + ..., where x0,x1,... <- xs
        = sum(log(x | x <- xs))

和

x = exp(log(x))

即exp & log 是反函数。

另外，请注意 log 未定义为 0 和负值。

结合这两个身份，我们得到:

exp(SUM(ln(x) | x <- xs, x > 0)) === prod(xs)

因此，我们可以将查询写成:

WITH united AS (
    SELECT 
      EXTRACT(YEAR FROM initial_release) yr
    , id
    , initial_users :: NUMERIC users
    , 1.0 change
    FROM programming_language

    UNION ALL

    SELECT 
      survey_year yr
    , language_id id
    , null
    , case when increase_or_decrease 
        then 1 + (percent_users_change / 100.0)
      else 1 - (percent_users_change / 100.0) end change
    FROM usage_data
)
SELECT yr, id, 
FIRST_VALUE(users) OVER w * EXP(SUM(LN(u.change)) OVER w) users
FROM united u
WINDOW w as (PARTITION BY id ORDER BY yr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

这里先统一初始数据和后期使用数据计算change列，然后从初始users计算连续年份的users 。