bigquery：如何找到按比例缩放的平均周销售额？

jq6vz3qz 于 2021-07-29 发布在 Java

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

要解决的场景：我们的最终目标是通过以下公式获得缩放平均周销售额（aws）：

Scaled AWS = (Number of Weeks article has had a record within the store / maximum Number of Weeks in data) * (AWS /  Number of Weeks)

输入“表a”结构：

Store   Article     Date    Week    Year    Sales
xx      xx          xx      xx      xx      xx

试用版：在找到aws（在商店级别）之前，已经构建了一个查询，并被困在那里继续进行。因为我是新手，所以我不确定如何通过bigquerysql实现这种扩展。需要帮助！

WITH total_weekly_sales AS (
SELECT
  Store,
  Week,
  SUM(Sales) AS TotalWeeklySales
FROM
  table_a
GROUP BY
  Store,
  Week)
SELECT
  Store,
  AVG(TotalWeeklySales) AS AverageWeeklySales
FROM
  total_weekly_sales tws
GROUP BY
  Store

样本数据：

Store   Article     Date            Week        Year    Sales
11      aa          2019-07-01      202001      2020    4.9
11      bb          2019-07-07      202001      2020    22.5
11      cc          2019-07-08      202002      2020    10.4
12      aa          2019-07-01      202001      2020    5.3
12      bb          2019-07-07      202001      2020    20.2

注解查询：
aws-指平均每周销售额[根据商店层面的给定数据得出，如我在报告中所述 Tried Out '讨论中的会话，如在商店级别查找totalweeklysale并获得平均值，则会给出aws]
周数-在商店级别[对于每个商店，显示多少周的数据。例如，对于存储11，此变量值为2，而对于存储12，此变量值为1]
数据中的最大周数-例如：根据我们的样本数据，此值为2[说明：对于每个门店，必须计算当前数据的总周数，并取所有门店中的最大周数。在这里，11号店总共有2周，而12号店总共有1周。所以，这里的最大值是2，它被指定为这个变量的值]
期望结果：按比例缩放的aws公式第1部分结果[文章在存储中有记录的周数/数据中的最大周数]

Store   part_1_value    
11      1.5 (3/2)
12      1 (2/2)

比例aws公式第2部分结果[aws/周数]

Store   part_2_value    
11      18.9 (37.8/2)
12      25.5 (25.5/1)

最终输出：【第1部分*第2部分】

Store   ScaledAverageWeeklySales    
11      28.35 (1.5*18.9)
12      25.5 (1*25.5)

总的来说，我们的最终结果应该是在商店层面。提前谢谢！

sql group-by google-bigquery subquery week-number

来源：https://stackoverflow.com/questions/63148063/bigquery-how-to-find-scaled-average-weekly-sales

1条答案

按热度按时间

bqjvbblv1#

在您的澄清之后，我能够创建一个查询来计算您要查找的内容。
我使用了您提供的数据以及with语句，以及bigquery中的max、count聚合内置方法。下面是查询，

WITH unique_articles AS (
SELECT Store, COUNT(DISTINCT Article) as uniq_art FROM `test-proj-261014.bq_load_codelab.sales_week`
GROUP BY Store
),
max_weeks_st AS (

# not possible to use DATE_DIFF cos the format of the date, the number of weeks would be 1 for the sample data

# so WEEK column will be used instead

SELECT Store, COUNT(Distinct Week) as max_weeks_st  FROM `test-proj-261014.bq_load_codelab.sales_week` 
group by Store
), 

# below returns a struct. So in order to access the int64 value name_of_struct.max_weeks_data

max_weeks_data AS(
SELECT MAX(a.max_weeks_st) AS max_weeks_data FROM max_weeks_st a
),
sum_sales AS (
SELECT Store, SUM(sales) as sum_sales  FROM `test-proj-261014.bq_load_codelab.sales_week` 
GROUP BY Store
), 
final_data AS(
select a.Store, a.uniq_art, b.max_weeks_st,c.sum_sales, max_weeks_data  FROM  unique_articles a LEFT JOIN sum_sales c USING(Store)
LEFT JOIN max_weeks_st b USING(Store) CROSS JOIN  max_weeks_data 
)
SELECT Store,((uniq_art/max_weeks_data.max_weeks_data)*(sum_sales/ max_weeks_st)) as AWS_result  FROM final_data a

以及输出，

Row Store   AWS_result
1   11      28.35
2   12      25.5

请注意，max\u weeks\u data是一个结构。因此，要访问其值，必须遵循语法：name\u of \u struct.max\u weeks\u data。此外，请注意，您描述的每个变量都是在一个temp表中计算的，总是带有用于联接表的store id。

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

我来回答

bigquery：如何找到按比例缩放的平均周销售额？

1条答案

相关问题

热门标签

最新问答