SQL的第一个日期第一次出现的值 [英] SQL first date for the first occurrence of a value

问题描述

我有一张与客户有关的表格以及他们的评分历史(低，中，高)，每季度进行一次评估.

I have a table with clients and the history of their rating (low, medium, high) which gets assessed quarterly.

示例表:

client_ID  rating  eff_from
111  high  30.09.2018
111  high  30.06.2018
111  medium  31.03.2018
111  high  31.12.2017
111  high  30.09.2017
111  low  30.06.2017
222  medium  30.09.2018
222  high  30.06.2018
222  high  31.03.2018
222  low  31.12.2017
222  low  30.09.2017
222  medium  30.06.2017

我希望获得最新评级的最低eff_from日期.在上表中，客户端111的日期为2018年6月30日，客户端222的日期为2018年9月30日.

I would like to get the minimum eff_from date for the latest rating. From the table above, that would be 30.06.2018 for client 111 and 30.09.2018 for client 222.

诀窍在于，评级可能会发生变化，例如从高到中，然后再回到高，因此按客户和评级进行分组并采用最小eff_from日期是行不通的-客户端111的结果为30.09.2017.

The trick is that the rating can change for example from high to medium and back to high, so grouping by client and rating and taking the minimum eff_from date doesn't work - it would result in 30.09.2017 for client 111.

环境是一个DB2数据库，并且eff_from字段的格式设置为日期.

The environment is a DB2 database and the eff_from field is formatted as date.

有什么想法吗?

推荐答案

这是解决问题的一种方法:

Here's one way to tackle your problem:

CREATE TABLE #T (ClientID INT, RATING VARCHAR (20), eff_From DATE)

INSERT INTO #T VALUES
(111, 'high',  '20180930'  ), 
(111, 'high',  '20180630'  ) ,
(111, 'medium', '20180331' ) ,
(111, 'high',  '20171231'  ) ,
(111, 'high', '20170930'      ) ,
(111, 'low', '20170630'   ); 

WITH CTE AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY  eff_From) AS RowNumb
    FROM #T
)

SELECT C.*, 
      C2.RATING AS C2Rating,
      C2.eff_From AS C2EffFrom,
      CASE WHEN C.RATING <> C2.RATING THEN 1 ELSE 0 END AS RatingChanged 
INTO #T2
FROM CTE AS C
LEFT JOIN CTE AS C2 ON C.RowNumb = C2.RowNumb - 1

SELECT ClientID, MAX (C2EffFrom) AS MaxEffFrom
FROM #T2
WHERE RatingChanged = 1
GROUP BY ClientID

这篇关于SQL的第一个日期第一次出现的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！