Teradata SQL中如何根据BEGINNING_DATE变化获取前序GRADE值

原始数据 #

期望结果 #

按ID分组，当BEGINNING_DATE变化时获取前序GRADE值：

尝试的SQL（未得到期望结果） #

SELECT
A.*,
LAG(GRADE) OVER (PARTITION BY ID,BEGINNING_DATE ORDER BY ENDING_DATE) AS LAG_GRADE
FROM 
DATA A

解决方案 #

通过先提取每个ID和BEGINNING_DATE的唯一组合，计算该组合对应的前序GRADE，再关联回原始数据，即可得到所有重复行的对应前序值：

WITH DATA AS (
  SELECT DISTINCT
   ID,
   GRADE,
   BEGINNING_DATE
FROM
    grade_data
),
LAG_DATA AS (
SELECT
  D.*,
  LAG(D.GRADE) OVER (PARTITION BY D.ID ORDER BY D.BEGINNING_DATE ) AS LAG_GRADE
FROM
DATA D
)
SELECT 
  GRADE.*,
  LD.LAG_GRADE
FROM
grade_data GRADE
LEFT JOIN LAG_DATA LD ON GRADE.ID=LD.ID AND GRADE.BEGINNING_DATE = LD.BEGINNING_DATE

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