在Databricks中使用SQL获取case_version_status_dt最大日期的问题

你的查询无法拿到正确的最大日期，核心问题是子查询的GROUP BY包含了case_version_id、CUSTOMER_REQUEST_ID等过多字段，这会让MAX(case_version_status_dt)只能计算每个细分分组内的最大值，而不是每个case_id对应的全局最大日期。

下面是修正后的两种方案：

方案一：精准关联最大日期对应的完整记录 #

如果需要获取每个case_id最大状态日期对应的所有case_version字段，用这个查询：

SELECT 
    cm.case_id,
    cm.user_case_id,
    cmr.CREATE_USER_ID,
    cm.CASE_MANAGER_USER_ID,
    DATE_FORMAT(cv_max.max_status_dt, "MM/dd/yyyy") AS case_version_status_dt,
    DATE_FORMAT(cmr.CASE_VERSION_MILESTONE_DT, "MM/dd/yyyy") AS CASE_VERSION_MILESTONE_DT
FROM dsams.case_master AS cm
LEFT JOIN dsams.case_usage_indicator AS cu ON cm.case_usage_indicator_cd = cu.case_usage_indicator_cd
-- 先单独计算每个case_id的最大状态日期
LEFT JOIN (
    SELECT 
        case_id, 
        MAX(case_version_status_dt) AS max_status_dt
    FROM dsams.case_version
    WHERE case_version_type_cd IN ("A","M","B","I")
      AND case_version_status_cd NOT IN ("CL","X")
    GROUP BY case_id
) AS cv_max ON cm.case_id = cv_max.case_id
-- 通过case_id和最大日期关联回case_version，拿到对应记录的其他字段
LEFT JOIN dsams.case_version AS cv 
    ON cm.case_id = cv.case_id 
    AND cv.case_version_status_dt = cv_max.max_status_dt
    AND cv.case_version_type_cd IN ("A","M","B","I")
    AND cv.case_version_status_cd NOT IN ("CL","X")
LEFT JOIN dsams.case_version_milestone AS cvm ON cv.case_version_id = cvm.case_version_id
LEFT JOIN dsams.case_milestone_revision AS cmr ON cvm.case_milestone_id = cmr.case_milestone_id
LEFT JOIN dsams.milestone AS m ON cvm.milestone_id = m.milestone_id
LEFT JOIN dsams.CUSTOMER_ORGANIZATION co ON co.CUSTOMER_ORGANIZATION_ID = cm.CUSTOMER_ORGANIZATION_ID
LEFT JOIN dsams.CUSTOMER_ORGANIZATION_SERVICE cos ON cos.CUSTOMER_ORGANIZATION_ID = co.CUSTOMER_ORGANIZATION_ID
LEFT JOIN dsams.CUSTOMER_SERVICE_TYPE cst ON cst.CUSTOMER_SERVICE_TYPE_ID = cos.CUSTOMER_SERVICE_TYPE_ID
LEFT JOIN dsams.CUSTOMER_REQUEST c ON c.CUSTOMER_REQUEST_ID = cv.CUSTOMER_REQUEST_ID
LEFT JOIN dsams.CASE_VERSION_STATUS cvs ON cvs.case_version_status_cd = cv.case_version_status_cd
LEFT JOIN dsams.CASE_VERSION_TYPE cvt ON cvt.CASE_VERSION_TYPE_CD = cv.case_version_type_cd
WHERE cm.user_case_id = "AJPTAB"
  AND m.MILESTONE_ID = "MILAP"
  AND cst.SERVICE_TYPE_TITLE_NM = "Navy"
  AND cm.case_usage_indicator_cd IN ("C","P")
  AND cm.SECURITY_ASSISTANCE_PROGRAM_CD = "FMS"
  AND cm.CASE_MASTER_STATUS_CD IN ("N","P")
ORDER BY case_version_status_dt

方案二：仅获取最大日期（简化版） #

如果只需要每个case_id的最大状态日期，不需要严格关联对应记录的其他字段，可以用这个简化查询：

SELECT 
    cm.case_id,
    cm.user_case_id,
    cmr.CREATE_USER_ID,
    cm.CASE_MANAGER_USER_ID,
    DATE_FORMAT(cv_max.max_status_dt, "MM/dd/yyyy") AS case_version_status_dt,
    DATE_FORMAT(cmr.CASE_VERSION_MILESTONE_DT, "MM/dd/yyyy") AS CASE_VERSION_MILESTONE_DT
FROM dsams.case_master AS cm
LEFT JOIN dsams.case_usage_indicator AS cu ON cm.case_usage_indicator_cd = cu.case_usage_indicator_cd
-- 单独计算每个case_id的最大状态日期
LEFT JOIN (
    SELECT 
        case_id, 
        MAX(case_version_status_dt) AS max_status_dt
    FROM dsams.case_version
    WHERE case_version_type_cd IN ("A","M","B","I")
      AND case_version_status_cd NOT IN ("CL","X")
    GROUP BY case_id
) AS cv_max ON cm.case_id = cv_max.case_id
LEFT JOIN dsams.case_version AS cv ON cm.case_id = cv.case_id
LEFT JOIN dsams.case_version_milestone AS cvm ON cv.case_version_id = cvm.case_version_id
LEFT JOIN dsams.case_milestone_revision AS cmr ON cvm.case_milestone_id = cmr.case_milestone_id
LEFT JOIN dsams.milestone AS m ON cvm.milestone_id = m.milestone_id
LEFT JOIN dsams.CUSTOMER_ORGANIZATION co ON co.CUSTOMER_ORGANIZATION_ID = cm.CUSTOMER_ORGANIZATION_ID
LEFT JOIN dsams.CUSTOMER_ORGANIZATION_SERVICE cos ON cos.CUSTOMER_ORGANIZATION_ID = co.CUSTOMER_ORGANIZATION_ID
LEFT JOIN dsams.CUSTOMER_SERVICE_TYPE cst ON cst.CUSTOMER_SERVICE_TYPE_ID = cos.CUSTOMER_SERVICE_TYPE_ID
LEFT JOIN dsams.CUSTOMER_REQUEST c ON c.CUSTOMER_REQUEST_ID = cv.CUSTOMER_REQUEST_ID
LEFT JOIN dsams.CASE_VERSION_STATUS cvs ON cvs.case_version_status_cd = cv.case_version_status_cd
LEFT JOIN dsams.CASE_VERSION_TYPE cvt ON cvt.CASE_VERSION_TYPE_CD = cv.case_version_type_cd
WHERE cm.user_case_id = "AJPTAB"
  AND m.MILESTONE_ID = "MILAP"
  AND cst.SERVICE_TYPE_TITLE_NM = "Navy"
  AND cv.case_version_type_cd IN ("A","M","B","I")
  AND cm.case_usage_indicator_cd IN ("C","P")
  AND cm.SECURITY_ASSISTANCE_PROGRAM_CD = "FMS"
  AND cm.CASE_MASTER_STATUS_CD IN ("N","P")
  AND cv.case_version_status_cd NOT IN ("CL","X")
ORDER BY case_version_status_dt

关键说明 #

• 子查询cv_max仅按case_id分组，确保拿到每个case的全局最大状态日期。
• 方案一中通过case_id和max_status_dt双重关联，保证拿到的是对应最大日期的那条记录，避免出现多条重复数据。

内容的提问来源于stack exchange，提问作者loucap68