如何按条件从多个类别中找到日期

Question

我需要在不同类别中找到数据：基于条件的断开，势能和PR。前两个类别很好。数据是一对一映射。问题是公关类别。此类别中有几个日期。我需要在此类别中找到最小的(MilestoneDate(，但是最小的(Milesotonedate(必须比今天最接近今天。

。

Following are table data:
Site   Building       MilestoneID   MilestoneType  MilestoneDate
 AGE     building1     MIC.MS.1000    Breakground    1/1/18
 AGE     building1     MIC.ACT.1000   Energization   2/3/19
 AGE     building1     PTM.B1.1130    PR             2/5/19
 AGE     building1     PTM.B1.1140    PR             3/4/20
 AGE     building1     PTM.B1.1150    PR             12/25/20
 AGE     building1     PTM.B1.1160    PR             5/25/22
 AGE     building2     MIC.MS.1000    Breakground    2/2/19
 AGE     building2     MIC.ACT.1000   Energization   12/5/19
 AGE     building2     PTM.B2.1130    PR             3/5/20
 AGE     building2      PTM.B2.1140   PR             8/10/20
 AGE     building2      PTM.B2.1150   PR             3/4/21
 AGE     building2      PTM.B2.1160   PR             5/4/22
 OCA     building1     MIC.MS.1000    Breakground    4/2/19
 OCA     building1     MIC.ACT.1000   Energization   5/4/20  
 OCA     building1      PTM.B1.1130   PR             6/8/21
 OCA     building1     PTM.B1.1140    PR             12/2/21
 OCA     building1     PTM.B1.1150    PR             3/5/22
 OCA     building1     PTM.B1.1160    PR             12/30/22

管理视图(( 选择 * 来自Management_view_nancy m 左加入 (( 选择 * 从 ( 选择ProjectObjectId， ID为activity_id， 终结为Milestonedate， 名称为里程碑 来自活动 其中ds = (( 选择最大(DS( 从活动( 联盟全部 选择ProjectObjectId， ID为activity_id， 最小(终结日期(作为终点， 姓名 来自活动 其中ID中的ID('LRP-RVA-B3-FS1000'， " LRP-RVA-B3-PR1000"， 'LRP-RVA-B3-PR1500'( 和ProjectObjectId = 4908 projectObjectId的组， ID， 名称((P 在try_cast(m.objectid as double(= p.projectObjection 和m.id = p.Activity_id(，PR为(( 选择 *， row_number((over(按场地进行分区，建筑物，milestoneType订单by try(cast(substr(milestonedate，1，10(作为日期((((RN 来自ManagementView 在哪里尝试(cast(substr(milestonedate，1，10(作为日期((> = current_date 和修剪(MilestoneType(='pr'(选择网站， 建筑， ID， MilestoneType， 里程碑， Milestonedate来自ManagementViewMilestoneType中的位置 "能量"(联盟全部选择网站， 建筑， ID， MilestoneType， 里程碑， Milestonedate来自PR其中rn = 1

查询工作正常，但是没有得到我想要的结果。我需要公关类别中的最小值(Morestonedate(，而最小的(Morestonedate(应该比今天更大，但最接近今天。此查询仍然使我在公关类别中为我提供了多个迈尔斯通酸盐。任何建议都将不胜感激

Answer 1

我建议使用CTE引入您要保留的PR数据点：

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Site, Building ORDER BY MilestoneDate) rn
    FROM managementview
    WHERE MilestoneDate >= CURRENT_DATE AND milestonetype = 'PR'
)
SELECT site, building, id, milestonetype, milestone, milestonedate
FROM managementview
WHERE milestonetype IN ('Breakground', 'Energization')
UNION ALL
SELECT site, building, id, milestonetype, milestone, milestonedate
FROM cte
WHERE rn = 1;

如果您的Presto版本不支持CTE，请尝试将其插入：

SELECT site, building, id, milestonetype, milestone, milestonedate
FROM managementview
WHERE milestonetype IN ('Breakground', 'Energization')
UNION ALL
SELECT site, building, id, milestonetype, milestone, milestonedate
FROM
(
    SELECT m.*,
        ROW_NUMBER() OVER (PARTITION BY Site, Building ORDER BY MilestoneDate) rn
    FROM managementview m
    WHERE MilestoneDate >= CURRENT_DATE AND milestonetype = 'PR'
) t
WHERE rn = 1;