部分去归一化独角兽观测
有许多研究人员在复活节岛上观察世界上最后剩下的独角兽1。 研究人员每天记录他们所看见的独角兽，目击日期，每个独角兽拥有的婴儿数量以及发现目标时是否喝醉。 它们分别上传到一个中央位置，然后每天向我提供所有新观察结果的平面文件。
我有一张看起来像这样的表格来包含信息：
create table unicorn_observations (
observer_id number not null
, unicorn_id number not null
, created date not null -- date the record was inserted into the database
, lastseen date not null -- date the record was last seen
, observation_date date not null
, no_of_babies number not null
, drunk varchar2(1) not null
, constraint pk_uo primary key ( observer_id, unicorn_id, created )
, constraint chk_uo_babies check ( no_of_babies >= 0 )
, constraint chk_uo_drunk check ( drunk in ('y','n') )
);
该表在observer_id ， unicorn_id和observation_date或lastseen上分别是唯一的。
有时管理数据输出的Cobold [原文如此]会稍微错误，重新输出相同的数据两次。 在这种情况下，我更新lastseen而不是创建新记录。 我只在每列相同的情况下这样做
不幸的是，研究人员并没有完全意识到第三种正常形式。 即使没有新的观察结果，他们每个月都会上传一些独角兽的前几个月的观测数据。 他们用新的observation_date做到这一点，这意味着一个新的记录被插入到表中。
我有一个单独的created和lastseen完全可追溯性研究人员有时会提出一些意见已晚。 这些由数据库创建，不属于提交的信息。
以下是一些示例数据（部分更改了列名以使其无需滚动条即可适用）。
+--------+--------+-----------+-----------+-----------+---------+-------+ | OBS_ID | UNI_ID | CREATED | LASTSEEN | OBS_DATE | #BABIES | DRUNK | +--------+--------+-----------+-----------+-----------+---------+-------+ | 1 | 1 | 01-NOV-11 | 01-NOV-11 | 31-OCT-11 | 10 | n | | 1 | 2 | 01-NOV-11 | 01-NOV-11 | 31-OCT-11 | 10 | n | | 1 | 3 | 01-NOV-11 | 01-NOV-11 | 31-OCT-11 | 10 | n | | 1 | 6 | 10-NOV-11 | 10-NOV-11 | 07-NOV-11 | 0 | n | | 1 | 1 | 17-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | | 1 | 2 | 17-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | | 1 | 3 | 17-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | | 1 | 6 | 17-NOV-11 | 17-NOV-11 | 17-NOV-11 | 0 | n | | 1 | 6 | 01-DEC-11 | 01-DEC-11 | 01-DEC-11 | 0 | n | | 1 | 6 | 01-JAN-12 | 01-JAN-12 | 01-JAN-12 | 3 | n | | 1 | 6 | 01-FEB-12 | 01-FEB-12 | 01-FEB-12 | 0 | n | | 1 | 6 | 01-MAR-12 | 01-MAR-12 | 01-MAR-12 | 0 | n | | 1 | 6 | 01-APR-12 | 01-APR-12 | 01-APR-12 | 0 | n | | 1 | 1 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | | 1 | 2 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | | 1 | 3 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | | 1 | 6 | 01-MAY-12 | 01-MAY-12 | 01-MAY-12 | 0 | n | +--------+--------+-----------+-----------+-----------+---------+-------+
我想对这些观察进行部分去规范化处理，以便如果使用相同的observer_id ， unicorn_id ， no_of_babies和drunk （有效载荷）接收到新记录，但使用更新的observation_date ，则更新表中的新列last_observation_date ，而不是插入新纪录。 我仍然会更新lastseen在这种情况下。
我需要这样做，因为我有许多复杂的独角兽相关的查询加入到此表中; 研究人员上传旧观测数据，每月约10万次，每月收到约900万条真实新纪录。 我已经跑了一年，已经有225米独角兽观察。 由于我只需要知道每个有效载荷组合的最后一次观察日期，我宁可大量减少表格的大小，并为自己节省大量时间对其进行全面扫描。
这意味着表格将变成：
create table unicorn_observations (
observer_id number not null
, unicorn_id number not null
, created date not null -- date the record was inserted into the database
, lastseen date not null -- date the record was last seen
, observation_date date not null
, no_of_babies number not null
, drunk varchar2(1) not null
, last_observation_date date
, constraint pk_uo primary key ( observer_id, unicorn_id, created )
, constraint chk_uo_babies check ( no_of_babies >= 0 )
, constraint chk_uo_drunk check ( drunk in ('y','n') )
);
存储在表中的数据如下所示; 如果观察只被“看过”一次， last_observation_date是否为空并不重要。 在加载数据时我不需要帮助，只需要将当前表的部分非规范化看起来像这样。
+--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | OBS_ID | UNI_ID | CREATED | LASTSEEN | OBS_DATE | #BABIES | DRUNK | LAST_OBS_DT | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | 1 | 6 | 10-NOV-11 | 01-DEC-11 | 07-NOV-11 | 0 | n | 01-DEC-11 | | 1 | 1 | 01-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | 31-OCT-11 | | 1 | 2 | 01-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | 31-OCT-11 | | 1 | 3 | 01-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | 31-OCT-11 | | 1 | 6 | 01-JAN-12 | 01-JAN-12 | 01-JAN-12 | 3 | n | | | 1 | 6 | 01-FEB-12 | 01-MAY-12 | 01-FEB-12 | 0 | n | 01-MAY-12 | | 1 | 1 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | | | 1 | 2 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | | | 1 | 3 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+
显而易见的答案
select observer_id as obs_id
, unicorn_id as uni_id
, min(created) as created
, max(lastseen) as lastseen
, min(observation_date) as obs_date
, no_of_babies as "#BABIES"
, drunk
, max(observation_date) as last_obs_date
from unicorn_observations
group by observer_id
, unicorn_id
, no_of_babies
, drunk
不起作用，因为它忽略了2012年1月1日单独观察三只独角兽宝宝麒麟6; 这又意味着， lastseen为11月10日创建的记录是不正确。
+--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | OBS_ID | UNI_ID | CREATED | LASTSEEN | OBS_DATE | #BABIES | DRUNK | LAST_OBS_DT | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | 1 | 1 | 01-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | 31-OCT-11 | | 1 | 2 | 01-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | 31-OCT-11 | | 1 | 3 | 01-NOV-11 | 17-NOV-11 | 09-APR-11 | 10 | n | 31-OCT-11 | | 1 | 6 | 10-NOV-11 | 01-MAY-12 | 07-NOV-11 | 0 | n | 01-MAY-12 | | 1 | 6 | 01-JAN-12 | 01-JAN-12 | 01-JAN-12 | 3 | n | 01-JAN-12 | | 1 | 1 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | 19-APR-12 | | 1 | 2 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | 19-APR-12 | | 1 | 3 | 19-APR-12 | 19-APR-12 | 19-APR-12 | 7 | y | 19-APR-12 | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+
没有一些程序逻辑，即循环，我目前还没有看到这样做的方法。 我宁愿避免在这种情况下的循环，因为我将不得不全面扫描一个225米的行表260次（不同的created日期的数量）。 即使使用lag()和lead()也需要递归，因为每个独角兽都有不确定的观察量。
有没有在单个SQL语句中创建这个数据集的方法？
表规范和示例数据也在SQL小提琴中。
试图更好的解释：
问题在于什么时候是真的。 2012年1月1日，独角兽6有3个婴儿。
在由GROUP BY创建的“表”中查看只有独角兽6; 如果我在1月1日试图找到婴儿的数量，我会得到两条记录，这是一个矛盾。
+--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | OBS_ID | UNI_ID | CREATED | LASTSEEN | OBS_DATE | #BABIES | DRUNK | LAST_OBS_DT | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | 1 | 6 | 10-NOV-11 | 01-MAY-12 | 07-NOV-11 | 0 | n | 01-MAY-12 | | 1 | 6 | 01-JAN-12 | 01-JAN-12 | 01-JAN-12 | 3 | n | 01-JAN-12 | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+
但是，我希望只有一行，如第二个表格。 在这里，对于任何时间点，最多只有一个“正确的”价值，因为独角兽6有0个婴儿的两个时间段在它有3个的那一天已被分成两排。
+--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | OBS_ID | UNI_ID | CREATED | LASTSEEN | OBS_DATE | #BABIES | DRUNK | LAST_OBS_DT | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+ | 1 | 6 | 10-NOV-11 | 01-DEC-11 | 07-NOV-11 | 0 | n | 01-DEC-11 | | 1 | 6 | 01-JAN-12 | 01-JAN-12 | 01-JAN-12 | 3 | n | | | 1 | 6 | 01-FEB-12 | 01-MAY-12 | 01-FEB-12 | 0 | n | 01-MAY-12 | +--------+--------+-----------+-----------+-----------+---------+-------+-------------+
1.围着摩艾放牧
基于我认为你想要做的事情，主要是关于麒麟6的具体问题的更新，我认为这会得到你想要的结果。 它不需要递归lead和lag ，但确实需要两个级别。
select *
from (
select observer_id, unicorn_id,
case when first_obs_dt is null then created
else lag(created) over (order by rn) end as created,
case when last_obs_dt is null then lastseen
else lead(lastseen) over (order by rn) end as lastseen,
case when first_obs_dt is null then observation_date
else lag(observation_date) over (order by rn)
end as observation_date,
no_of_babies,
drunk,
case when last_obs_dt is null then observation_date
else null end as last_obs_dt
from (
select observer_id, unicorn_id, created, lastseen,
observation_date, no_of_babies, drunk,
case when lag_no_babies != no_of_babies or lag_drunk != drunk
or lag_obs_dt is null then null
else lag_obs_dt end as first_obs_dt,
case when lead_no_babies != no_of_babies or lead_drunk != drunk
or lead_obs_dt is null then null
else lead_obs_dt end as last_obs_dt,
rownum rn
from (
select observer_id, unicorn_id, created, lastseen,
observation_date, no_of_babies, drunk,
lag(observation_date)
over (partition by observer_id, unicorn_id, no_of_babies,
drunk
order by observation_date) lag_obs_dt,
lag(no_of_babies)
over (partition by observer_id, unicorn_id, drunk
order by observation_date) lag_no_babies,
lag(drunk)
over (partition by observer_id, unicorn_id, no_of_babies
order by observation_date) lag_drunk,
lead(observation_date)
over (partition by observer_id, unicorn_id, no_of_babies,
drunk
order by observation_date) lead_obs_dt,
lead(no_of_babies)
over (partition by observer_id, unicorn_id, drunk
order by observation_date) lead_no_babies,
lead(drunk)
over (partition by observer_id, unicorn_id, no_of_babies
order by observation_date) lead_drunk
from unicorn_observations
order by 1,2,5
)
)
where first_obs_dt is null or last_obs_dt is null
)
where last_obs_dt is not null
order by 1,2,3,4;
这使：
OBSERVER_ID UNICORN_ID CREATED LASTSEEN OBSERVATI NO_OF_BABIES D LAST_OBS_
----------- ---------- --------- --------- --------- ------------ - ---------
1 1 17-NOV-11 01-NOV-11 09-APR-11 10 n 31-OCT-11
1 1 19-APR-12 19-APR-12 19-APR-12 7 y 19-APR-12
1 2 17-NOV-11 01-NOV-11 09-APR-11 10 n 31-OCT-11
1 2 19-APR-12 19-APR-12 19-APR-12 7 y 19-APR-12
1 3 17-NOV-11 01-NOV-11 09-APR-11 10 n 31-OCT-11
1 3 19-APR-12 19-APR-12 19-APR-12 7 y 19-APR-12
1 6 10-NOV-11 01-DEC-11 07-NOV-11 0 n 01-DEC-11
1 6 01-JAN-12 01-JAN-12 01-JAN-12 3 n 01-JAN-12
1 6 01-FEB-12 01-MAY-12 01-FEB-12 0 n 01-MAY-12
9 rows selected.
它有独角兽6的三条记录，但第三条的lastseen和observation_date与您的样本相反，所以我不确定我是否还不理解这一点。 我假设你想保留的最早observation_date和最新lastseen每个分组内，理由是它似乎是添加新记录时，会发生什么，但我不知道...
因此，最内层的查询从表中获取原始数据，并使用略有不同的分区来获取observation_date和no_of_babies和drunk列的lead和lag 。 order by是这样一个rownum可以稍后使用，在下一步中获得并用于后面的顺序。 为了简洁起见，为了麒麟6：
CREATED LASTSEEN OBSERVATI NO_OF_BABIES D LAG_OBS_D LAG_NO_BABIES L LEAD_OBS_ LEAD_NO_BABIES L
--------- --------- --------- ------------ - --------- ------------- - --------- -------------- -
10-NOV-11 10-NOV-11 07-NOV-11 0 n 17-NOV-11 0 n
17-NOV-11 17-NOV-11 17-NOV-11 0 n 07-NOV-11 0 n 01-DEC-11 0 n
01-DEC-11 01-DEC-11 01-DEC-11 0 n 17-NOV-11 0 n 01-FEB-12 3 n
01-JAN-12 01-JAN-12 01-JAN-12 3 n 0 0
01-FEB-12 01-FEB-12 01-FEB-12 0 n 01-DEC-11 3 n 01-MAR-12 0 n
01-MAR-12 01-MAR-12 01-MAR-12 0 n 01-FEB-12 0 n 01-APR-12 0 n
01-APR-12 01-APR-12 01-APR-12 0 n 01-MAR-12 0 n 01-MAY-12 0 n
01-MAY-12 01-MAY-12 01-MAY-12 0 n 01-APR-12 0 n
如果num_of_babies或drunk值发生了变化，下一个级别lead num_of_babies observation_date的lead值和lag值 - 您只专门提到分裂婴儿计数，但我认为你也想在清醒时分裂。 在此之后，任何对first_obs_date或last_obs_date都为null的last_obs_date都是小范围的开始或结束。
CREATED LASTSEEN OBSERVATI NO_OF_BABIES D FIRST_OBS LAST_OBS_ RN
--------- --------- --------- ------------ - --------- --------- ----------
10-NOV-11 10-NOV-11 07-NOV-11 0 n 17-NOV-11 1
17-NOV-11 17-NOV-11 17-NOV-11 0 n 07-NOV-11 01-DEC-11 2
01-DEC-11 01-DEC-11 01-DEC-11 0 n 17-NOV-11 3
01-JAN-12 01-JAN-12 01-JAN-12 3 n 4
01-FEB-12 01-FEB-12 01-FEB-12 0 n 01-MAR-12 5
01-MAR-12 01-MAR-12 01-MAR-12 0 n 01-FEB-12 01-APR-12 6
01-APR-12 01-APR-12 01-APR-12 0 n 01-MAR-12 01-MAY-12 7
01-MAY-12 01-MAY-12 01-MAY-12 0 n 01-APR-12 8
任何不是小范围的开始或结束的东西现在都可以忽略，因为这些值与之前或之后的值相同或被其取代。 这涉及到观测问题的数量不确定 - 在这一点上你忽略了多少无关紧要。 因此，下一个级别通过筛选first_obs_dt和last_obs_dt非空的行来消除这些中间值。 在这个过滤后的集合中，有第二层lead和lag以获得每个日期的第一个或最后一个值 - 这是我不确定的是正确的，因为它与您的某个样本不匹配。
CREATED LASTSEEN OBSERVATI NO_OF_BABIES D LAST_OBS_
--------- --------- --------- ------------ - ---------
10-NOV-11 01-DEC-11 07-NOV-11 0 n
10-NOV-11 01-DEC-11 07-NOV-11 0 n 01-DEC-11
01-JAN-12 01-JAN-12 01-JAN-12 3 n 01-JAN-12
01-FEB-12 01-MAY-12 01-FEB-12 0 n
01-FEB-12 01-MAY-12 01-FEB-12 0 n 01-MAY-12
最后剩余的没有last_obs_dt被过滤掉。
现在我会等待，看看我误解了哪些位... * 8-）
在对lead和lag订单进行更正之后，每个阶段对于独角兽1的相同信息：
CREATED LASTSEEN OBSERVATI NO_OF_BABIES D LAG_OBS_D LAG_NO_BABIES L LEAD_OBS_ LEAD_NO_BABIES L
--------- --------- --------- ------------ - --------- ------------- - --------- -------------- -
17-NOV-11 17-NOV-11 09-APR-11 10 n 31-OCT-11 10 n
01-NOV-11 01-NOV-11 31-OCT-11 10 n 09-APR-11 10 n
19-APR-12 19-APR-12 19-APR-12 7 y
CREATED LASTSEEN OBSERVATI NO_OF_BABIES D FIRST_OBS LAST_OBS_ RN
--------- --------- --------- ------------ - --------- --------- ----------
17-NOV-11 17-NOV-11 09-APR-11 10 n 31-OCT-11 1
01-NOV-11 01-NOV-11 31-OCT-11 10 n 09-APR-11 2
19-APR-12 19-APR-12 19-APR-12 7 y 3
CREATED LASTSEEN OBSERVATI NO_OF_BABIES D LAST_OBS_
--------- --------- --------- ------------ - ---------
17-NOV-11 17-NOV-11 09-APR-11 10 n 09-APR-11
19-APR-12 19-APR-12 19-APR-12 7 y 19-APR-12
我不知道shoudl与保存发生什么observation_date和lastseen当原始数据输入失序这个样子，或者你会在将来添加新的记录，这种情况下做的。
尝试这个。
with cte as
(
select v.*, ROW_NUMBER() over (partition by grp, unicorn_id order by grp, unicorn_id) rn
from
(
select u.*,
ROW_NUMBER() over (partition by unicorn_id order by no_of_babies, drunk, created )
-ROW_NUMBER() over (partition by unicorn_id order by created) as grp
from unicorn_observations u
) v
)
select
observer_id, cte.unicorn_id, mincreated,maxlastseen,minobsdate,no_of_babies,drunk,maxobsdate
from cte
inner join
(
select
unicorn_id, grp,
min(created) as mincreated,
max(lastseen) as maxlastseen,
min(observation_date) as minobsdate,
max(observation_date) as maxobsdate
from cte
group by unicorn_id, grp
) v
on cte.grp = v.grp
and cte.unicorn_id = v.unicorn_id
where rn=1
order by created;
这种类型的问题可以通过首先在子查询中创建一些标志然后使用它们来解决。
with obs_flags as (
select
observer_id as obs_id
, unicorn_id as uni_id
, case when lag(observation_date) over (
partition by unicorn_id, no_of_babies, drunk
order by unicorn_id, observation_date
) is null then 1 else 0 end as group_start
, case when lead(observation_date) over (
partition by unicorn_id, no_of_babies,drunk
order by unicorn_id, observation_date
) is null then 1 else 0 end as group_end
, observation_date
, no_of_babies
, drunk
, lastseen
, created
from unicorn_observations
)
select obs_start.obs_id
, obs_start.uni_id
, obs_start.created
, obs_end.lastseen as lastseen
, obs_start.observation_date
, obs_start.no_of_babies as "#BABIES"
, obs_start.drunk
, obs_end.observation_date as last_obs_date
from obs_flags obs_start
join obs_flags obs_end on
obs_start.group_start = 1 and
obs_end.group_end = 1 and
obs_start.uni_id = obs_end.uni_id and
obs_start.no_of_babies = obs_end.no_of_babies and
obs_start.drunk = obs_end.drunk and
obs_start.observation_date <= obs_end.observation_date and
--Only join with the first end point we find:
not exists (
select * from obs_flags f where
obs_start.uni_id = f.uni_id and
obs_start.no_of_babies = f.no_of_babies and
obs_start.drunk = f.drunk and
f.group_end = 1 and
f.observation_date < obs_end.observation_date and
f.observation_date >= obs_start.observation_date
);
这是一个复杂的问题; 我可能还没有完全满足您的要求（或者可能会有错别字，我没有Oracle来测试它）。 但是，它应该让你知道它是如何完成的。
基本上，您首先找到您感兴趣的时段的所有开始和结束记录，然后将每个开始记录加入同一分组中的下一个结束记录。
更新：我的原始代码没有检查到开始后是否结束。 我修正了这一点。
Update2：正如Ben指出的那样， not exists子句在这里会很慢。 过去帮助我加快速度的另一种方法是分两步进行：先找到所有可能的配对，然后再单独选择正确的配对。
在这种情况下，在临时表或子查询中，将每个obs_start到每个可能正确的obs_end 。
然后，在这些配对之中，为每个obs_start选择具有最早obs_end的obs_start 。