需要针对上一年的特定日期范围的结果 - bigquery

Question

试图找到一种方法在bigquery中将我的结果与前一年进行比较，我已经检查了SO，但没有运气。有什么想法吗？

SELECT store_name,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE() then (sales) else 0 end) as 30_day_sales,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE() then orders else 0 end) as 24_hours_orders,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND CURRENT_DATE() then orders else 0 end) as 3_days_orders,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() then orders else 0 end) as 7_days_orders
from pbca
WHERE date(transaction_date)>="2018-01-01"
group by store_name
ORDER BY 30_day_sales

我想找到一种方法来逐年比较，并可能将 transaction_date>="2018-01-01" 更改为当前年份，这样我就不需要每年都更改它。

预期成果，Store_name|30_day_sales|24_hours_orders|3_days_orders|7_days_orders|30_day_sales_lastyear|24_hours_orders_lastyear|3_days_orders_lastyear|7_days_orders_lastyear

Answer 1

您可以使用类似于下面的 SQL 的内容，您可以在其中拥有一个表，您可以在其中存储最新日期，并使用相关查询从另一个表中获取最新日期并将其与主表进行比较。

WITH temp_data AS (
SELECT  timestamp('2019-10-12') AS transaction_date, 'Electronic' as store_name , 100 as sales, 50 as orders UNION ALL
SELECT  timestamp('2019-10-05') AS transaction_date, 'Sports' as store_name, 101 as sales, 51 as orders  UNION ALL
SELECT  timestamp('2019-10-03') AS transaction_date, 'Media' as store_name, 102 as sales, 52 as orders  
)
SELECT store_name,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE() then (sales) else 0 end) AS day_sales_30 ,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE() then orders else 0 end) AS hours_orders_24,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND CURRENT_DATE() then orders else 0 end) AS days_orders_3,
sum(case when DATE(transaction_date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() then orders else 0 end) AS days_orders_7
from temp_data
WHERE DATE(transaction_date) >= (SELECT DATE_SUB(CURRENT_DATE() , INTERVAL 180 DAY ))
GROUP BY store_name