我希望在sql中为值的平均值构建一个case语句。

Question

我有一组客户的数据，订单的数量和订单的价值。我试图根据客户的平均订单价值将数据分成5段。

select case
         when avg(total_amount) <= 3.5 then
          '<3.5'
         when avg(total_amount) > 3.5 and avg(total_amount) <= 4.5 then
          ' 3.5-4.5'
         when avg(total_amount) > 4.5 and avg(total_amount) <= 6 then
          ' 4.5-6'
         when avg(total_amount) > 6 and avg(total_amount) <= 8 then
          '6-8'
         else
          'over 8'
       end as bucket,
       count(customer_id),
       avg(total_amount),
       avg(order_count)
  from (select customer_account_id,
               sum(spent amount) as total_amount,
               count(order_id) as order_count
          from data_table
         group by customer_account_id)
 group by bucket;

我只想要5行输出，显示每个桶中的花费和订单。

Answer 1

您可以尝试这样的方法(未经测试)：

WITH list AS
( SELECT customer_account_id as customer_id,
         sum(spent_amount) as total_amount,
         count(order_id) as order_count
    FROM data_table
   GROUP BY customer_account_id
)
SELECT CASE
         when range = numrange (0, 3.5, '[]') then '<3.5'
         when range = numrange (3.5, 4.5, '(]') then '3.5-4.5'
         when range = numrange (4.5, 6.0, '(]') then '4.5-6'
         when range = numrange (6.0, 8.0, '(]') then '6-8'
         else 'over 8'
       END AS bucket,
       count(customer_id),
       avg(total_amount),
       avg(order_count)
  FROM list
 INNER JOIN (VALUES (numrange (0, 3.5, '[]')),(numrange (3.5, 4.5, '(]')),(numrange (4.5, 6.0, '(]')),(numrange (6.0, 8.0, '(]')),(numrange (8.0, NULL, '(]'))) AS range
    ON range @> total_amount
 GROUP BY range