bigquery：用groupby选择前三天的销售额，并将其保存在单独的列中

qvsjd97n 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(377)

输入：有表'a'

Store   Category   Sales   Day
11      aaa        1.5     Sunday
11      aaa        0.5     Monday
11      aaa        2.5     Tuesday
11      aaa        2.0     Wednesday
11      aaa        3.0     Thursday
11      aaa        3.5     Friday
11      aaa        0.5     Saturday
22      bbb        0.5     Sunday
22      bbb        1.5     Monday
22      bbb        2.3     Tuesday
22      bbb        0.3     Wednesday
22      bbb        1.4     Thursday
22      bbb        4.1     Friday
22      bbb        0.2     Saturday

场景：必须取按门店、类别分组的销售平均值，并在单独的列中保存，同时选择前3个销售日并在单独的列中保存。结果，一行对应一个商店，分类如下所示。
预期产量：

Store   Category   AvgSales   PeakDay1   PeakDay2   PeakDay3   
11      aaa        1.92       Friday     Thursday   Tuesday
22      bbb        1.47       Friday     Tuesday    Monday

已尝试查询：

SELECT
  Store,
  Category,
  avg(Sales) as AvgSales,
  ARRAY_AGG(Sales ORDER BY Sales DESC LIMIT 3)  #but this line will not produce results in 3 separate columns
FROM A
GROUP BY Site, Category

提前谢谢！

sql group-by google-bigquery average array-agg

来源：https://stackoverflow.com/questions/62938926/bigquery-select-top-3-day-of-sales-with-groupby-and-save-in-separate-columns-le

2条答案

按热度按时间

amrnrhlw1#

下面是bigquery标准sql


# standardSQL

SELECT Store, Category, AvgSales,
  Days[OFFSET(0)] PeakDay1,
  Days[SAFE_OFFSET(1)] PeakDay2,
  Days[SAFE_OFFSET(2)] PeakDay3
FROM (
  SELECT Store, Category, 
    ROUND(AVG(Sales), 2) AvgSales,
    ARRAY_AGG(Day ORDER BY Sales DESC LIMIT 3) Days
  FROM `project.dataset.table` t
  GROUP BY Store, Category
)

如果要应用到问题输出的样本数据

Row Store   Category    AvgSales    PeakDay1    PeakDay2    PeakDay3     
1   11      aaa         1.93        Friday      Thursday    Tuesday  
2   22      bbb         1.47        Friday      Tuesday     Monday

赞(0）回复(0）举报 2021-07-26

z9zf31ra2#

你可以使用数组agg，但是 row_number() 看起来很简单：

select store, category, avg(sales),
       max(case when seqnum = 1 then day end) as peakday1,
       max(case when seqnum = 2 then day end) as peakday2,
       max(case when seqnum = 3 then day end) as peakday3
from (select store, category, day, 
             row_number() over (partition by store, category order by sales desc) as seqnum
      from a
     ) a
group by store, category;

如果要将其放入数组列中，可以使用：

SELECT Store, Category, avg(Sales) as AvgSales,
       ARRAY_AGG(day ORDER BY Sales DESC LIMIT 3) 
FROM A
GROUP BY Site, Category

赞(0）回复(0）举报 2021-07-26

我来回答

bigquery：用groupby选择前三天的销售额，并将其保存在单独的列中

2条答案

相关问题

热门标签

最新问答