时间戳

首页 > 解决方案 > 选择 2 个日期之间的记录平均值 SQL Netezza

sql - 选择 2 个日期之间的记录平均值 SQL Netezza

问题描述

我有 2 个表，第一个称为Activations，并且有两列：Line_ID、Activation_Date。第二个名为Speed的表有列：Line_ID、From_Date、To_Date、Record。

第一个表样本：

|Line_ID| Activation_Date|
|-------+----------------|
|123456 | 1-Jan          |
|345678 | 2-Jan          |
|987654 | 3-Jan          |
...

第二个缺口和孤岛表：

|Line_ID|From_Date| To_Date |Speed|
|-------+---------+---------+-----|  
|123456 |1-Jan    |4-Jan    |70   |
|123456 |4-Jan    |7-Jan    |51   |
|123456 |7-Jan    |10-Jan   |48   |
|123456 |10-Jan   |15-Jan   |40   |
|123456 |15-Jan   |17-Jan   |70   |
|123456 |17-Jan   |19-Jan   |54   |
|123456 |19-Jan   |21-Jan   |94   |
|123456 |21-Jan   |28-Jan   |91   |
|123456 |28-Jan   |31-Jan   |35   |
...

我需要将 Activation 表与 Records 表一起添加 4 列到 Activation 表中，但有一些顾虑，

结果将如下所示

|Line_ID| Activation_Date|AVG_SPEED_Week1|AVG_SPEED_Week2|AVG_SPEED_Week3|AVG_SPEED_Week4|
|-------+----------------+---------------+---------------+---------------+---------------|
|123456 | 1-Jan          |60.5           |44             |72.6           |91             |
...

结果探索

AVG_SPEED_Week1: Average of Speed in the 1st 7 days starting Records.From_Date: 1-Jan Records.To_Date: 7-Jan
AVG_SPEED_Week2: Average of Speed in the 2nd 7 days starting Records.From_Date: 8-Jan Records.To_Date: 14-Jan
AVG_SPEED_Week3: Average of Speed in the 2nd 7 days starting Records.From_Date: 15-Jan Records.To_Date: 21-Jan
AVG_SPEED_Week4: Average of Speed in the 2nd 7 days starting Records.From_Date: 22-Jan Records.To_Date: 28-Jan

标签： sqlnetezzagaps-and-islands

解决方案

I wasn't able to test it but how about that?:

SELECT a.Line_ID
,a.Activation_Date 
,CASE WHEN a.Activation_Date >= s.From_Date AND a.Activation_Date <= s.To_Date AND DATEADD(day,-7,s.To_Date) >= a.Activation_Date THEN AVG(SUM(s.Speed)) END AVG_SPEED_Week1
,CASE WHEN a.Activation_Date >= s.From_Date AND a.Activation_Date <= s.To_Date AND DATEADD(day,-14,s.To_Date) >= a.Activation_Date AND DATEADD(day,-7,s.From_Date) >= a.Activation_Date THEN AVG(SUM(s.Speed)) END AVG_SPEED_Week2
,CASE WHEN a.Activation_Date >= s.From_Date AND a.Activation_Date <= s.To_Date AND DATEADD(day,-21,s.To_Date) >= a.Activation_Date AND DATEADD(day,-14,s.From_Date) >= a.Activation_Date THEN AVG(SUM(s.Speed)) END AVG_SPEED_Week3
,CASE WHEN a.Activation_Date >= s.From_Date AND a.Activation_Date <= s.To_Date AND DATEADD(day,-28,s.To_Date) >= a.Activation_Date AND DATEADD(day,-21,s.From_Date) >= a.Activation_Date THEN AVG(SUM(s.Speed)) END AVG_SPEED_Week4
FROM Activations a
JOIN Speed s 
ON a.Line_ID=s.Line_ID
GROUP BY a.Line_ID, a.Activation_Date

I assumed that you don't need to dynamicaly count and generate average speed by any number of weeks, that 4 weeks is enough.

It definitely needs testing.

推荐阅读