sql - Oracle 基于时间的分析

Question

我需要计算按特定自定义时间段汇总的汇总统计信息。具体来说，一家链式餐厅每天 24 小时营业。我需要按时段计算总销售额等统计数据，其中时段是“早餐”、“午餐”、“晚餐”和“过夜”。对于这家公司来说，他们跟踪统计数据的正式日子在晚饭后开始。因此，构成正式日的 24 小时时间段从晚上 8 点开始，一直持续到第二天 CST 晚上 8 点)。那是一个时期。另一个时期是“过夜”，从晚上 8 点到凌晨 5 点 30 分运行。我将这些定义放入名为“tdef”的表中，如下所示:

drop table tdef cascade constraints 
;

create table tdef 
(
    cd char(3) not null,
    start_ts date not null,
    stop_ts date not null 
)

然后我将定义插入到 tdef 表中，存储为开始日期总是从 1900 年 1 月 1 日开始的日期，如果它跨越午夜，那么它在 1900 年 1 月 2 日结束。像这样，

insert into tdef (start_ts, stop_ts, cd) 
values
(
to_date('1900/01/01 20:00:00', 'yyyy/mm/dd hh24:mi:ss'),
to_date('1900/01/02 19:59:59', 'yyyy/mm/dd hh24:mi:ss'),
'24H'
);

insert into tdef (start_ts, stop_ts, cd) 
values
(
to_date('1900/01/01 10:30:00', 'yyyy/mm/dd hh24:mi:ss'),
to_date('1900/01/01 13:29:59', 'yyyy/mm/dd hh24:mi:ss'),
'LUN
);

insert into tdef (start_ts, stop_ts, cd) 
values
(
to_date('1900/01/01 15:30:00', 'yyyy/mm/dd hh24:mi:ss'),
to_date('1900/01/02 08:29:59', 'yyyy/mm/dd hh24:mi:ss'),
'ON'
);

我有一个非常大的表(大约 25 亿行)，其中包含所有注册交易。我需要按日期(他们的定义是晚上 8 点到晚上 8 点)、产品和时间维度汇总销售情况，并将其存储在表格中以便快速访问报告。该表应如下所示:

Dec 12 2011, Hamburger, 24H, 1000
Dec 12 2011, Hamburger, ON, 100
Dec 12 2011, Hamburger, LUN, 400

这是我为实现这一目标所做的工作，我在交易表中添加了两个日期列，分别是 1/1/1900 和 1/2/1900 的交易时间，如下所示:

to_date(concat('01/01/1900 ', tran_tm), 'mm/dd/yyyy hh24:mi'),
to_date(concat('01/02/1900 ', tran_tm), 'mm/dd/yyyy hh24:mi')

我索引了这两列。然后我创建了一个交叉查找表，将事务 ID 与时间码相关联。每个事务代码可以被多次定义。所以它看起来像这样:

24H, 1
24H, 2
24H, 3
...
LUN, 100
LUN, 101
LUN, 102
...
ON, 1
ON, 2
...

我使用了两个插入选择语句来完成此操作:

select  t.trans_id, td.cd, to_date(to_char(to_date(concat(to_char(ts, 'mm/dd/yyyy '), to_char(td.stop_ts, 'hh24:mi:ss')), 'mm/dd/yyyy hh24:mi:ss', 'yyyymmdd'), 'yyyymmdd')
from trans t, tdef td
where ts1 >= td.start_ts and ts1 <= td.stop_ts

select  t.trans_id, td.cd, to_date(to_char(to_date(concat(to_char(ts, 'mm/dd/yyyy '), to_char(td.stop_ts, 'hh24:mi:ss')), 'mm/dd/yyyy hh24:mi:ss', 'yyyymmdd'), 'yyyymmdd')
from trans t, tdef td
where ts2 >= td.start_ts and ts2 <= td.stop_ts

第三个字段是“官方日期”。其工作方式是假设交易发生在 12/12/2011 8:01PM，那么 ts1 字段将是 1/1/1900 8:01PM，ts2 字段将是 1/2/1900 8:01PM。在第一个查询中，该字段将连接到 cd '24H' 和 'ON'。官方日期将计算为 12/13/2011 为 '24H' 和 12/13/2011 为 'ON'。此事务不会加入第二个查询，因为它超出了日期范围。假设交易发生在 12/13/2011 12:05PM。在第一个查询中，ts1 会像这样加入:'24H' 表示日期 12/13/2011，'LUN' 表示日期 12/13/2011。

一旦我有了这张表，就很容易聚合:

select tdef_trans.dt, sum(sales) from trans, tdef_trans where trans.id = tdef_trans.id and tdef_trans.cd = 'LUN'

虽然这个解决方案似乎有效，但我打赌有一种更优雅的方法来做到这一点。有任何想法吗？

Answer 1

如果您正在尝试进行数据仓库(听起来很像)，那么您可能会发现制作一个包含一天中每一秒以及它属于哪个时期的表最容易。那将只有 86400 行。

那么你的查询就变成了这个时间维度的一个相对简单的连接