postgresql 按收入和年份对用户进行分组

yeotifhr 于 2023-04-11 发布在 PostgreSQL

关注(0)|答案(1)|浏览(95)

我有问题分组用户在'Group_3'在2连续年.想法是组用户在第3组下订单是超过2000年收入在2连续年.

SELECT email_id,
       COUNT(*) as num_of_orders,
       SUM(revenue) AS total_money_spent,
       CASE WHEN 
                SUM(CASE WHEN order_creation_date BETWEEN '2016-01-01' and '2016-12-   31'
                THEN revenue END) > 2000
       THEN 'Group_1'
            WHEN 
                SUM(CASE WHEN order_creation_date BETWEEN '2015-01-01' and '2016-12-31'
                THEN revenue END) > 2000
       THEN 'Group_2'
            WHEN 
                SUM(CASE WHEN EXTRACT(years FROM order_creation_date) <> LAG(EXTRACT(years FROM order_creation_date)) 
                OVER(PARTITION BY email_id ORDER BY EXTRACT(years FROM order_creation_date)) + 1 
                THEN revenue END) > 2000
                THEN 'Group_3'
            WHEN 
                SUM(CASE WHEN order_creation_date BETWEEN '2015-01-01' and '2016-12-31'
                THEN revenue END) < 2000
       THEN 'Group_4'
            WHEN 
                SUM(CASE WHEN order_creation_date BETWEEN '2015-01-01' and '2016-12-31'
                THEN revenue END) = 0
       THEN 'Group_5'
         END
FROM sql_test
GROUP BY email_id
ORDER BY num_of_orders DESC;

我得到：
聚合函数调用不能包含窗口函数调用
第13行：...E WHEN EXTRACT（years FROM order_creation_date）〈〉LAG（EXTRAC..
我猜我不能在CASE查询中使用LAG？

postgresql

来源：https://stackoverflow.com/questions/75904817/group-users-by-revenue-and-years

1条答案

按热度按时间

4ioopgfo1#

窗口函数在SELECT语句中 * 在 * 聚合函数之后执行。你不能以相反的顺序嵌套它们。考虑事件的顺序：

在应用LIMIT之前获取结果计数的最佳方法

请改用子查询：

SELECT email_id
     , COUNT(*) as num_of_orders
     , SUM(revenue) AS total_money_spent
     , CASE
          WHEN SUM(revenue) FILTER (WHERE order_creation_date BETWEEN '2016-01-01' AND '2016-12-31') > 2000 THEN 'Group_1'
          WHEN SUM(revenue) FILTER (WHERE order_creation_date BETWEEN '2015-01-01' AND '2016-12-31') > 2000 THEN 'Group_2'
          WHEN SUM(revenue) FILTER (WHERE EXTRACT(year FROM order_creation_date) <> last_year + 1)   > 2000 THEN 'Group_3'
          WHEN SUM(revenue) FILTER (WHERE order_creation_date BETWEEN '2015-01-01' AND '2016-12-31') < 2000 THEN 'Group_4'
          WHEN SUM(revenue) FILTER (WHERE order_creation_date BETWEEN '2015-01-01' AND '2016-12-31') = 0    THEN 'Group_5'
       END AS my_sum
FROM  (
   SELECT *, LAG(EXTRACT(year FROM order_creation_date)) OVER (PARTITION BY email_id ORDER BY EXTRACT(year FROM order_creation_date)) AS last_year
   FROM   sql_test
   ) sub
GROUP  BY email_id
ORDER  BY num_of_orders DESC;

当你在它：聚合FILTER表达式比那些CASE表达式更优雅和更快。请参阅：

使用其他（不同）筛选器聚合列

赞(0）回复(0）举报 2023-04-11

我来回答

postgresql 按收入和年份对用户进行分组

1条答案

相关问题

热门标签

最新问答