分时间段统计交易数据

select a.shop_id,a.source_name,a.shop_jde_number,a.shop_open_date,
       case when crt_date >= shop_open_date and crt_date<=3_days then '3_days'
            when crt_date > 3_days and crt_date<=one_week then '3_to_one'
            when crt_date > one_week and crt_date<=two_week then 'two_week'
            when crt_date > two_week and crt_date<=three_week then 'three_week'
            when crt_date > two_week and crt_date<=three_week then 'three_week'
            when crt_date > three_week and crt_date<=30_days then 'three_to_30'
            when crt_date > 30_days and crt_date<=60_days then '30_to_60'
            when crt_date > 60_days and crt_date<=90_days then '60_to_90'
            when crt_date > 90_days and crt_date<=120_days then '90_to_120'
            end as period
            ,deliver_type
            ,sum(trade_amount),sum(order_quantity)
from (
select shop_id,source_name,shop_jde_number,shop_open_date,
ADDDATE(shop_open_date,INTERVAL 3 day) as 3_days,
ADDDATE(shop_open_date,INTERVAL 1 week) as one_week,
ADDDATE(shop_open_date,INTERVAL 2 week) as two_week,
ADDDATE(shop_open_date,INTERVAL 3 week) as three_week,
ADDDATE(shop_open_date,INTERVAL 30 day) as 30_days,
ADDDATE(shop_open_date,INTERVAL 60 day) as 60_days,
ADDDATE(shop_open_date,INTERVAL 90 day) as 90_days,
ADDDATE(shop_open_date,INTERVAL 120 day) as 120_days
from bigdata.dwd_shop_info_daily dsid 
where report_date >='2021-01-01'
and shop_open_date >='2021-01-01'
and shop_id  in (715,927,968)
group by 1,2,3
) a 
left join 
(
select  crt_date,shop_id,source_name,deliver_type,
sum(trade_amount) as trade_amount,sum(order_quantity) as order_quantity
from bigdata.dwd_trades_info_finaly_view  dssd 
where crt_date>='2021-01-01'
group by 1,2,3,4
)b 
on a.shop_id=b.shop_id  and a.source_name=b.source_name
and b.crt_date>=a.shop_open_date 
and b.crt_date<=a.120_days 
group by 1,2,3,4,5,6

### 实现指定时间段数据统计 为了在 SQL Server 中实现指定时间段内的数据统计,可以采用多种方法来满足不同的业务需求。对于需要填充缺失日期并显示零值的情况,可以通过创建一个包含所需日期范围的日历表来进行左连接操作。 #### 创建日历表 首先定义一个临时表或公共表表达式(CTE),用于生成目标时间段内的每一天记录: ```sql WITH DateRange AS ( SELECT CAST('2023-01-01' AS DATE) AS dt UNION ALL SELECT DATEADD(DAY, 1, dt) FROM DateRange WHERE dt < '2023-12-31' ) SELECT * FROM DateRange; ``` 此查询会生成从 `2023-01-01` 到 `2023-12-31` 的每日列表[^1]。 #### 进行数据统计 假设有一个名为 `Sales` 的销售记录表,其中包含字段 `SaleDate` 和 `Amount` 。要统计每个月份的总销售额,并且当某个月没有发生任何交易时返回金额为零,则可执行如下命令: ```sql WITH MonthlySales AS ( WITH DateRange AS ( SELECT CAST('2023-01-01-01' AS DATE)) AS MonthEnd UNION ALL SELECT DATEADD(MONTH, 1, MonthStart), EOMONTH(DATEADD(MONTH, 1, MonthStart)) FROM DateRange WHERE MonthStart < '2023-12-01' ) SELECT d.MonthStart, ISNULL(SUM(s.Amount), 0) TotalSales FROM DateRange d LEFT JOIN Sales s ON s.SaleDate BETWEEN d.MonthStart AND d.MonthEnd GROUP BY d.MonthStart ) SELECT * FROM MonthlySales ORDER BY MonthStart; ``` 这段脚本不仅实现了对特定年份各月度销售总额的汇总,还通过外键关联确保即使某些月份没有任何订单也能正确展示其对应的数值为零。 #### 使用窗口函数进行更复杂的析 如果希望进一步扩展功能,比如找出同一天中有多少名新入职员工的例子,可以根据给定的时间间隔应用窗口函数完成此类任务。下面是一个基于星期几和具体日子筛选重复雇佣事件的小例子: ```sql SELECT e.ename, e.hiredate, TO_CHAR(e.hiredate,'mon day') AS hire_weekday, COUNT(*) OVER(PARTITION BY FORMAT(e.hiredate,'MMM dd')) AS same_day_hires_count FROM emp e WHERE same_day_hires_count > 1; ``` 这里利用了 `FORMAT()` 函数代替原始示例中的 `TO_CHAR()` 来格式化日期字符串;同时注意这里的子查询被简化成了单条语句的形式[^2]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值