SQL -在group by之后应用两个不同的条件从SQL表中选择行

Question

我愿意根据客户端id，日期组筛选行

所以在组中如果最新状态为更新日期= 'CO'则按更新日期显示最早的行，如果最新状态为('NonPay'，'VD'，'Active')则按更新日期显示最近的行。

table1

<表类> rownum clientid 日期 状态 updateDate tbody><<tr>112342021-02-01.2021-02-01212342021-02-01.2021-01-01312342021-02-01非生产层2020-12-01412342021-02-03活动2021-11-01512342021-02-03.2021-10-01612342021-02-03.2021-09-01712342021-02-04.2021-08-01812342021-02-04VD2021-07-01945672019-06-01活动2020-12-281045672019-06-01.2020-12-201145672019-06-01非生产层2020-12-101245672019-05-03VD2020-12-011345672019-05-03活动2020-11-011445672019-05-03.2020-10-011545672019-05-03NP2020-09-011645672019-04-04.2020-08-011745672019-04-04VD2020-07-01

#Import the csv in df and try with below code...
grp = df.groupby(['clientid', 'date'], axis=0)
li = []
for i, j in grp:
j.sort_values(by=['updateDate'], ascending = True)
fil = j['status'] != 'CO'
j = j.loc[fil, :].reset_index(drop=True)
li.append(j.loc[0,:])
pd.DataFrame(li) 

解决MySQL问题的方法如下:

• 步骤1:获取每个分区(client_id, date)的第一个和最后一个updateDate
• 步骤2:获取('NonPay','VD','Active')
组的最后一个updateDate
• 步骤3:获取('CO')
组的第一个updateDate
• 步骤4:对
两个组的行进行合并

---

步骤1:您可以使用ROW_NUMBER():

• 在updateDate上递增查找该值等于1的分区的第一个日期
• 在updateDate上查找该值等于1的分区的最后日期

SELECT *,
ROW_NUMBER() OVER(PARTITION BY clientid, date
ORDER     BY updateDate     ) AS firstUpdateDate,
ROW_NUMBER() OVER(PARTITION BY clientid, date
ORDER     BY updateDate DESC) AS lastUpdateDate
FROM tab  

---

步骤2:当你在最后一个日期有一个状态('NonPay','VD','Active')时，你需要检索最后一个日期本身，因此获得相应的行意味着有效地检索最后一行(lastUpdateDate = 1)，其中状态是先前引用的一个。

SELECT rd.rownum, 
rd.clientid, 
rd.date, 
rd.status, 
rd.updateDate
FROM ranked_dates rd
WHERE rd.lastUpdateDate = 1
AND rd.status IN ('NonPay', 'VD', 'Active')

---

步骤3:当您在最后一个日期中有一个状态'CO'时，您需要检索第一个日期，或者换句话说，从我们拥有的所有第一个日期中，我们不希望那些(clientid,date)的组合已经从步骤2中捕获的行。您可以使用左联接，其中左表值为空(这些值在步骤2生成的表中没有对应关系)。

SELECT rd.rownum, 
rd.clientid, 
rd.date, 
rd.status, 
rd.updateDate
FROM       ranked_dates rd
LEFT JOIN np_vd_active_status s 
ON rd.clientid = s.clientid
AND rd.date = s.date
WHERE rd.firstUpdateDate = 1
AND s.rownum IS NULL

---

步骤4:只需在步骤2结果和步骤3结果之间应用一个并集。如果你想在rownum字段上做一些排序，你可以用ORDER BY语句轻松地做到这一点。

---

最终查询:

WITH ranked_dates AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY clientid, date
ORDER     BY updateDate     ) AS firstUpdateDate,
ROW_NUMBER() OVER(PARTITION BY clientid, date
ORDER     BY updateDate DESC) AS lastUpdateDate
FROM tab  
), np_vd_active_status AS (
SELECT rd.rownum, 
rd.clientid, 
rd.date, 
rd.status, 
rd.updateDate
FROM ranked_dates rd
WHERE rd.lastUpdateDate = 1
AND rd.status IN ('NonPay', 'VD', 'Active')
)
SELECT rd.rownum, 
rd.clientid, 
rd.date, 
rd.status, 
rd.updateDate
FROM       ranked_dates rd
LEFT JOIN np_vd_active_status s 
ON rd.clientid = s.clientid
AND rd.date = s.date
WHERE rd.firstUpdateDate = 1
AND s.rownum IS NULL
UNION
SELECT * 
FROM np_vd_active_status 

在这里试试。