请教高手这种join怎么办?

r
robustsong
楼主 (未名空间)

我现在有一个table,里面存了下面的信息
ID Event_Date Event_Desc
1 1/3/2020 Applied
1 1/5/2020 Approved
2 1/4/2020 Applied
2 1/6/2020 Denied
每个ID申请了之后,就会出现一个申请的日期,approved之后就会出现approved的日期。
但是这个table有个问题,我想选在1/4/2020有多少人applied/approved/denied.这个
是不行的

我现在想生成个带完全日期的记录。先生成一个日期 reference table

Date
1/1/2020
1/2/2020
1/3/2020

10/27/2020

跟上面的table join一下,变成下面的这个

Date ID Event_Desc
1/1/2020
1/2/2020
1/3/2020 1 Applied
1/4/2020 1 Applied
1/4/2020 2 Applied
1/5/2020 1 Approved
1/5/2020 2 Applied
1/6/2020 1 Approved
1/6/2020 2 Denied
1/7/2020 1 Approved
1/7/2020 2 Denied
1/8/2020 1 Approved
1/8/2020 2 Denied


这样子我就可以随意选任何的一天来分析当天的数据。之后还可以生成一个daily
trend chart.

请教一下,Oracle sql怎么实现这个join?谢谢

a
asshole

just generate a table first with all date. maybe a small loop to generate it?

you may find something online.

then use it to left join our own data table.
r
robustsong

date list我已经生成了。我现在的难点就是如何left join生成我想要的table。
简单的left join生成的结果跟原数据table没什么区别。
e
eagle7

先把日期和Event分开,生成两个不同的column。然后用新生成的日期来filter。

那个多少人的输出用count,可以count(ID),或者count(distinct ID)
P
PCB

"我想选在1/4/2020有多少人applied/approved/denied"
楼主究竟想要啥呢?

SELECT DISTINCT ID
FROM table
WHERE EVENT_DATE = '2020-01-04' AND EVENT_DESC IN ('applied', 'approved', 'denied')

不是你想要的结果
r
robustsong

我问的是如何来做这个join。join做好了,就可以选择某一天的数据进行分析,跟你写的这个类似。

比如ID = 1的这个人, 1月3号申请了一张信用卡,他就会在1/3/2020有一个applied的记录。1/5号信用卡批准,就会在1/5/2020有一个approved的记录。但是在原始的table里面,这个人就只有这么两个记录。如果想知道1月10号这个人的状态,这就不能直接
设定日期等于1/10/2020,必须要做一个比较,小于这个日期的是怎么样的。

我现在想通过join一个date list做一个全历史的table,这样子,申请人只要申请过以后,每天都会有记录,最开始是applied,等到decision变成approved后就一直是
approved。这个数据结果可以直接做data visualization了。

现在关键的问题是如何通过join来fill那些日期。简单的left join并不能fill这些日期

【 在 PCB (越学越笨) 的大作中提到: 】
: "我想选在1/4/2020有多少人applied/approved/denied"
: 楼主究竟想要啥呢?
: SELECT DISTINCT ID
: FROM table
: WHERE EVENT_DATE = '2020-01-04' AND EVENT_DESC IN ('applied', '
approved'
: , 'denied')
: 不是你想要的结果

r
robustsong

现在我的难点是如何在left join的时候fill那些在原始数据table里面没有记录的日期。

【 在 eagle7 (衣勾七) 的大作中提到: 】
: 先把日期和Event分开,生成两个不同的column。然后用新生成的日期来filter。
: 那个多少人的输出用count,可以count(ID),或者count(distinct ID)

P
PCB

so you want last available status, if there is any

SELECT T.[ID], T.[EVENT_DESC], TODAY()
FROM
table AS T
INNER JOIN
(
SELECT [ID], MAX([EVENT_DATE]) AS LAST_EVENT_DATE
FROM table
WHERE [EVENT_DATE] < TODAY()
GROUP BY [ID]
) AS LAST_EVENT_LIST ON T.[ID] = LAST_EVENT_LIST.[ID] AND T.[EVENT_DATE] =
LAST_EVBENT_LIST.[LAST_EVENT_DATE]

【 在 robustsong (莫非) 的大作中提到: 】
: 现在我的难点是如何在left join的时候fill那些在原始数据table里面没有记录的日期。

n
nmamtf

-- This script generate the date data in range:

SELECT TO_DATE ('01-JAN-2020') + ROWNUM - 1
FROM (SELECT ROWNUM
FROM ( SELECT 1
FROM DUAL
CONNECT BY LEVEL <=
(TO_DATE ('01-FEB-2020') - TO_DATE ('01-JAN-2020'))));

-- This script generate the user data :

CREATE TABLE ROBUSTSONG
(
ID INTEGER,
EVENT_DATE DATE,
EVENT_DESC VARCHAR2(100)
);

Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(1, TO_DATE('1/3/2020', 'MM/DD/YYYY'), 'Applied');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(1, TO_DATE('1/5/2020', 'MM/DD/YYYY'), 'Approved');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(2, TO_DATE('1/4/2020', 'MM/DD/YYYY'), 'Applied');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(2, TO_DATE('1/6/2020', 'MM/DD/YYYY'), 'Denied');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(3, TO_DATE('1/3/2020', 'MM/DD/YYYY'), 'Applied');
COMMIT;

-- This script generate the result :

SELECT A.*, B.* FROM

(SELECT TO_DATE ('01-JAN-2020') + ROWNUM - 1 AS EVENT_DATE
FROM (SELECT ROWNUM
FROM ( SELECT 1
FROM DUAL
CONNECT BY LEVEL <=
(TO_DATE ('01-FEB-2020') - TO_DATE ('01-JAN-2020')))) ) A,
ROBUSTSONG B

WHERE A.EVENT_DATE = B.EVENT_DATE(+)
ORDER BY A.EVENT_DATE, B.ID;

--out put

1/1/2020
1/2/2020
1/3/2020 1 1/3/2020 Applied
1/3/2020 3 1/3/2020 Applied
1/4/2020 2 1/4/2020 Applied
1/5/2020 1 1/5/2020 Approved
1/6/2020 2 1/6/2020 Denied
1/7/2020
1/8/2020
:
:
1/31/2020


Is this what you need?


【 在 robustsong (莫非) 的大作中提到: 】
: 我现在有一个table,里面存了下面的信息
: ID Event_Date Event_Desc
: 1 1/3/2020 Applied
: 1 1/5/2020 Approved
: 2 1/4/2020 Applied
: 2 1/6/2020 Denied
: 每个ID申请了之后,就会出现一个申请的日期,approved之后就会出现approved的日期。
: 但是这个table有个问题,我想选在1/4/2020有多少人applied/approved/denied.这个
: 是不行的
: 我现在想生成个带完全日期的记录。先生成一个日期 reference table
: ...................