一个选项使用条件聚合。这个想法是同时过滤两个季度的表，然后在
Q(编写查询以返回Territory和相应的Sales Growth(比较2019年第4季度和2019年第3季度的增长(。
给出的表格-
Cust_Sales: -Cust_id,product_sku,order_date,order_value,order_id,month
Cust_Territory: cust_id,territory_id,customer_city,customer_pincode
使用表FCT_CUSTOMER_SALES(每个客户都有销售额(和表MAP_CUSTOMER_TERRITORY(提供TERRITORY到CUSTOMER的映射(回答此问题。
Output format-
TERRITORY_ID | SALES_GROWTH
我的解决方案-
Select ((q2.claims - q1.claims)/q1.claims * 100) AS SALES_GROWTH , c.territory_id
From
(select sum(s.order_value) from FCT_CUSTOMER_SALES s inner join MAP_CUSTOMER_TERRITORY c on s.customer_id=c.customer_id where s.order_datetime between 1/07/2019 and 30/09/2019 group by c.territory_id) as q1.claims,
(select sum(s.order_value) from FCT_CUSTOMER_SALES s inner join MAP_CUSTOMER_TERRITORY c on s.customer_id=c.customer_id where s.order_datetime between 1/10/2019 and 31/12/2019 group by c.territory_id) as q2.claims
Group by c.territory_id
我的解决方案显示为不正确，我会请求任何可以帮助我解决方案的人，并让我知道我的错误在哪里
sum()聚合函数中使用case表达式来计算每个季度的销售额：
select
c.territory_id,
( sum(case when s.order_date >= date '2020-01-01' then s.order_value end)
- sum(case when s.order_date < date '2020-01-01' then s.order_value end)
) / (sum(case when s.order_date < date '2020-01-01' then s.order_value end)) * 100.0 as sales_growth
from fct_customer_sales s
inner join map_customer_territory c on s.customer_id = c.customer_id
where s.order_datetime >= date '2020-01-07' and s.order_datetime < '2020-01-01'
group by c.territory_id
您没有告诉您正在使用哪个数据库，而日期功能高度依赖于供应商。这使用标准的DATE语法来声明文字日期——如果您的数据库不支持，您可能需要修改它