我有一个非常简单的表:
CREATE TABLE T_ACTIVITY_LOG
(
ACTIVITY_DATE TIMESTAMP,
APPLIANCE_ID NUMBER(9) DEFAULT 0 NOT NULL,
APPLIANCE_STATUS NUMBER(1) DEFAULT 0 NOT NULL
)
记录各种工业设备的状态变化(-1和0)。每次状态被修改时，都会在表中插入一条记录:
APPLIANCE_ID ACTIVITY_DATE APPLIANCE_STATUS
---------- ------------------- -----------
1 01-jan-15 00:00:00 0
1 11-jan-15 00:00:00 1
1 21-jan-15 00:00:00 0
1 30-jan-15 00:00:00 1
现在我正在处理一个报告，我需要生成一个数据集，其中包含一个月中的每一天的一行和相应的设备状态:
APPLIANCE_ID ACTIVITY_DATE APPLIANCE_STATUS
---------- ------------------- -----------
1 01-jan-15 00:00:00 0
1 02-jan-15 00:00:00 0
1 03-jan-15 00:00:00 0
1 04-jan-15 00:00:00 0
1 05-jan-15 00:00:00 0
1 06-jan-15 00:00:00 0
1 07-jan-15 00:00:00 0
1 08-jan-15 00:00:00 0
1 09-jan-15 00:00:00 0
1 10-jan-15 00:00:00 0
1 11-jan-15 00:00:00 1
1 12-jan-15 00:00:00 1
1 13-jan-15 00:00:00 1
1 14-jan-15 00:00:00 1
1 15-jan-15 00:00:00 1
1 16-jan-15 00:00:00 1
1 17-jan-15 00:00:00 1
1 18-jan-15 00:00:00 1
1 19-jan-15 00:00:00 1
1 20-jan-15 00:00:00 1
1 21-jan-15 00:00:00 0
1 22-jan-15 00:00:00 0
1 23-jan-15 00:00:00 0
1 24-jan-15 00:00:00 0
1 25-jan-15 00:00:00 0
1 26-jan-15 00:00:00 0
1 27-jan-15 00:00:00 0
1 28-jan-15 00:00:00 0
1 29-jan-15 00:00:00 0
1 30-jan-15 00:00:00 1
1 31-jan-15 00:00:00 1
是否有可能通过SQL做到这一点，或者我应该在软件中使用临时表来构建数据集?
谢谢!
Oracle支持分区外连接语法，该语法允许在没有行可用的地方填充稀疏数据，并且在本教程中还提供了一种用最近值填充空白的方法。
要达到你的目标需要三个技巧:
- 生成日期列表
- 分区外连接
- LAST_VALUE()分析函数填充"缺失"值
:
with t_activity_log as (select 1 appliance_id, to_date('01/01/2015', 'dd/mm/yyyy') activity_date, 0 appliance_status from dual union all
select 1 appliance_id, to_date('11/01/2015', 'dd/mm/yyyy') activity_date, 1 appliance_status from dual union all
select 1 appliance_id, to_date('21/01/2015', 'dd/mm/yyyy') activity_date, 0 appliance_status from dual union all
select 1 appliance_id, to_date('30/01/2015', 'dd/mm/yyyy') activity_date, 1 appliance_status from dual union all
select 2 appliance_id, to_date('02/01/2015', 'dd/mm/yyyy') activity_date, 0 appliance_status from dual union all
select 2 appliance_id, to_date('10/01/2015', 'dd/mm/yyyy') activity_date, 1 appliance_status from dual union all
select 2 appliance_id, to_date('15/01/2015', 'dd/mm/yyyy') activity_date, 0 appliance_status from dual union all
select 2 appliance_id, to_date('26/01/2015', 'dd/mm/yyyy') activity_date, 1 appliance_status from dual),
dates as (select to_date('01/01/2015', 'dd/mm/yyyy') -1 + level dt
from dual
connect by level <= 31) -- query to generate the list of dates; amend as appropriate to get the list of dates you're after
select tal.appliance_id,
dts.dt,
last_value(tal.appliance_status ignore nulls) over (partition by tal.appliance_id order by dts.dt) appliance_status
from dates dts
left outer join t_activity_log tal partition by (tal.appliance_id) on (dts.dt = tal.activity_date)
order by tal.appliance_id,
dts.dt;
APPLIANCE_ID DT APPLIANCE_STATUS
------------ ---------- ----------------
1 01/01/2015 0
1 02/01/2015 0
1 03/01/2015 0
1 04/01/2015 0
1 05/01/2015 0
1 06/01/2015 0
1 07/01/2015 0
1 08/01/2015 0
1 09/01/2015 0
1 10/01/2015 0
1 11/01/2015 1
1 12/01/2015 1
1 13/01/2015 1
1 14/01/2015 1
1 15/01/2015 1
1 16/01/2015 1
1 17/01/2015 1
1 18/01/2015 1
1 19/01/2015 1
1 20/01/2015 1
1 21/01/2015 0
1 22/01/2015 0
1 23/01/2015 0
1 24/01/2015 0
1 25/01/2015 0
1 26/01/2015 0
1 27/01/2015 0
1 28/01/2015 0
1 29/01/2015 0
1 30/01/2015 1
1 31/01/2015 1
2 01/01/2015
2 02/01/2015 0
2 03/01/2015 0
2 04/01/2015 0
2 05/01/2015 0
2 06/01/2015 0
2 07/01/2015 0
2 08/01/2015 0
2 09/01/2015 0
2 10/01/2015 1
2 11/01/2015 1
2 12/01/2015 1
2 13/01/2015 1
2 14/01/2015 1
2 15/01/2015 0
2 16/01/2015 0
2 17/01/2015 0
2 18/01/2015 0
2 19/01/2015 0
2 20/01/2015 0
2 21/01/2015 0
2 22/01/2015 0
2 23/01/2015 0
2 24/01/2015 0
2 25/01/2015 0
2 26/01/2015 1
2 27/01/2015 1
2 28/01/2015 1
2 29/01/2015 1
2 30/01/2015 1
2 31/01/2015 1
NB。如果第一个appliance_date在日期周期的开始之后(参见2月1日的appliance_id = 2)，您没有说明应该发生什么，所以我将其默认为null。如果您不希望显示这些行，则必须在上述SQL周围抛出一个外部查询，以便在appliance_status不为null时进行过滤。
如果你想让它不同，你还必须修改生成日期的查询。在指定的开始日期和结束日期之间，或基于表数据等)。关于如何生成日期列表，有很多示例。