sql-server - 获取组中的最新行
问题描述
我目前正在使用我的 TSQL 数据库中的一系列表，这些表允许我获取有关投保人（或客户）的信息。我需要按出生日期和邮政编码开始对每个投保人进行分组，因为其他匹配不够可靠。
我目前遇到的问题是这将显示该组中的最大值，而不是最新值。遗憾的是，由于 GDPR 的原因，我无法提供任何数据，希望有人可以通过我的查询引导我朝着正确的方向前进。
询问：
SELECT MAX([MSM_MarketPricing].[EnquiryKey]) AS EnquiryKey
,MAX([TotalNumberOfQuotes]) AS TotalNumberOfQuotes
,MAX([MarketPriceTop5Annual]) AS MarketPriceTop5Annual
,MAX([DirectInsPriceTop5Annual]) AS DirectInsPriceTop5Annual
,MAX([BrokerPriceTop5Annual]) AS BrokerPriceTop5Annual
,MAX([BigBrandsPriceTop5Annual]) AS BigBrandsPriceTop5Annual
,MAX([MediumBrandsTop5Annual]) AS MediumBrandsTop5Annual
,MAX([LittleBrandsPriceTop5Annual]) AS LittleBrandsPriceTop5Annual
,MAX([MSM_YourQuotes].[YourAnnualPrice]) AS YourAnnualPrice
,' ' AS ' '
-- Number Prediction Calculations --
,MAX([MarketPriceTop5Annual]) * ((100 +
(CASE
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
ELSE -27.48 END)) / 100) AS 'MarketPriceFromBucket'
-- Number Prediction Calculations --
,MAX([DirectInsPriceTop5Annual]) * ((100 +
(CASE
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
ELSE -27.48 END)) / 100) AS 'DirectInsPriceFromBucket'
-- Number Prediction Calculations --
,MAX([BrokerPriceTop5Annual]) * ((100 +
(CASE
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
ELSE -27.48 END)) / 100) AS 'BrokerPriceFromBucket'
,' ' AS ' '
,CAST(MAX([MSM_YourQuotes].[YourAnnualPrice]) - MAX([MarketPriceTop5Annual]) * ((100 +
(CASE
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 0 AND 250 THEN -7.09
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 251 AND 500 THEN -8.52
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 501 AND 750 THEN -12.11
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 751 AND 1000 THEN -14.63
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1001 AND 1500 THEN -16.88
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 1501 AND 2000 THEN -18.00
WHEN LEAST(MAX([MarketPriceTop5Annual]), MAX([DirectInsPriceTop5Annual]), MAX([BrokerPriceTop5Annual]), MAX([BigBrandsPriceTop5Annual]), MAX([MediumBrandsTop5Annual]), MAX([LittleBrandsPriceTop5Annual])) BETWEEN 2001 AND 2500 THEN -19.42
ELSE -27.48 END)) / 100) AS INT) as 'CostDiffBucket'
,CAST(MAX([MSM_YourQuotes].[YourAnnualPrice]) - MAX([MarketPriceTop5Annual]) AS INT) as 'CostDiff'
,FLOOR(ROUND(100.0*(MAX([MSM_YourQuotes].[YourAnnualPrice]) - MAX([MarketPriceTop5Annual])) / MAX([MarketPriceTop5Annual]), 2)) AS PercentDiff
,CAST(CASE WHEN DATEADD(yy, DATEDIFF(yy, MAX([MSM_PolicyHolder].[DateOfBirth]), GETDATE()), MAX([MSM_PolicyHolder].[DateOfBirth])) < GETDATE() THEN DATEDIFF(yy, MAX([MSM_PolicyHolder].[DateOfBirth]), GETDATE()) ELSE DATEDIFF(yy, MAX([MSM_PolicyHolder].[DateOfBirth]), GETDATE()) - 1 END AS INT) AS 'Age'
,MAX([MSM_PolicyHolder].[DateOfBirth]) AS DateOfBirth
,MAX([MSM_PolicyHolder].[NoClaimsDiscountYears]) AS NoClaimsDiscountYears
,MAX([MSM_PolicyHolder].[LicenceHeldForMonths])/12 as 'LicenseLength'
,CAST(CASE WHEN DATEADD(yy, DATEDIFF(yy, MAX([MSM_Enquiry].[FirstStartedDrivingCar]), GETDATE()), MAX([MSM_Enquiry].[FirstStartedDrivingCar])) < GETDATE() THEN DATEDIFF(yy, MAX([MSM_Enquiry].[FirstStartedDrivingCar]), GETDATE()) ELSE DATEDIFF(yy, MAX([MSM_Enquiry].[FirstStartedDrivingCar]), GETDATE()) - 1 END AS INT) AS 'YearsOwned'
,MAX([MSM_Enquiry].[CarValue])
-- Sub-Filters
,MAX([MSM_PolicyHolder].[Gender]) AS Gender
,MAX([MSM_PolicyHolder].[MaritalStatus]) AS MaritalStatus
,MAX([MSM_PolicyHolder].[MainBusinessType]) AS MainBusinessType
,CASE WHEN MAX([MIU_BusinessCats].[FTBusinessType]) IS NULL then 'Other' ELSE MAX([MIU_BusinessCats].[FTBusinessType]) END AS 'OccupationIndustry'
,MAX([MSM_PolicyHolder].[MainEmploymentStatus]) AS MainEmploymentStatus
,MAX([MSM_PolicyHolder].[LicenceType]) AS LicenceType
,CASE WHEN MAX(CAST([MSM_PolicyHolder].[NonMotoringConvictions] AS INT)) = 1 THEN 'Yes' ELSE 'No' END AS NonMotoringConvictions
,MAX([MSM_PolicyHolder].[AccessToOtherCars]) AS AccessToOtherCars
,MAX([MSM_PolicyHolder].[UKResidentSince]) AS UKResidentSince
,CAST(CASE WHEN DATEADD(yy, DATEDIFF(yy, MAX([MSM_PolicyHolder].[UKResidentSince]), GETDATE()), MAX([MSM_PolicyHolder].[UKResidentSince])) < GETDATE() THEN DATEDIFF(yy, MAX([MSM_PolicyHolder].[UKResidentSince]), GETDATE()) ELSE DATEDIFF(yy, MAX([MSM_PolicyHolder].[UKResidentSince]), GETDATE()) - 1 END AS INT) AS 'YearsInTheUK'
,CASE WHEN MAX(CAST([MSM_PolicyHolder].[MainDriver] AS INT)) = 1 THEN 'Yes' ELSE 'No' END AS MainDriver
,CASE WHEN MAX([MSM_Enquiry].[FuelType]) = 'P' THEN 'Petrol' WHEN MAX([MSM_Enquiry].[FuelType]) = 'D' THEN 'Diesel' ELSE 'Electric' END AS 'FuelType'
,MAX([MSM_Enquiry].[BodyShape]) AS BodyShape
,MAX([MSM_Enquiry].[AnnualPersonalMileage]) + MAX([MSM_Enquiry].[AnnualBusinessMileage]) AS 'Mileage'
,MAX([MSM_Enquiry].[CoverType]) AS CoverType
,MAX([MIU_MSMSales].[InsuranceCo]) AS InsuranceCo
,MAX([MIU_MSMSales].[AreaGroupBandID]) AS AreaGroupBandID
,MAX([MIU_AreaGroupBand].[Description]) AS 'AreaBand'
,MAX([MIU_MSMSales].[HostedRatingEngineName])
,MAX([MIU_MSMSales].[CreditScore]) AS CreditScore
,MAX([MIU_MSMSales].[EngineCC]) AS EngineCC
,MAX([MIU_MSMSales].[Garaging]) AS Garaging
,MAX([MSM_Enquiry].[EnquiryDateTime]) AS EnquiryDateTime
,MAX(CAST([MSM_YourQuotes].[UsedGoToSite] AS INT)) AS 'Clicks'
,(SELECT TOP 1 COUNT(*) FROM [MIU_MSMSales] MSMS WHERE MAX([MSM_Enquiry].[PolicyStartDate]) >= MSMS.[CoverDate] AND MAX([MIU_MSMSales].[InsuranceCo]) = MSMS.[InsuranceCo] AND CONCAT(MSMS.[DateOfBirth], MSMS.[PostcodeOutward]) = CONCAT(MAX([MSM_PolicyHolder].[DateOfBirth]), MAX([MSM_PolicyHolder].[PostcodeOutward]))) Sales
,CONCAT(CONVERT(VARCHAR(8), MAX([MSM_PolicyHolder].[DateOfBirth]), 3), MAX([MSM_PolicyHolder].[PostcodeOutward])) as 'UniqueCode'
FROM [dbo].[MSM_MarketPricing]
LEFT OUTER JOIN [MSM_PolicyHolder] ON [MSM_MarketPricing].[EnquiryKey] = [MSM_PolicyHolder].[EnquiryKey]
LEFT OUTER JOIN [MSM_YourQuotes] ON [MSM_MarketPricing].[EnquiryKey] = [MSM_YourQuotes].[EnquiryKey]
LEFT OUTER JOIN [MSM_Enquiry] ON [MSM_MarketPricing].[EnquiryKey] = [MSM_Enquiry].[EnquiryKey]
LEFT OUTER JOIN [MSM_Sales] ON [MSM_MarketPricing].[EnquiryKey] = [MSM_Sales].[EnquiryKey]
LEFT OUTER JOIN [MIU_BusinessCats] ON [MSM_PolicyHolder].[MainBusinessType] = [MIU_BusinessCats].[FTBusinessCategory]
LEFT OUTER JOIN [MIU_MSMSales] ON [MSM_PolicyHolder].[DateOfBirth] = [MIU_MSMSales].[DateOfBirth] AND [MSM_PolicyHolder].[PostcodeOutward] = [MIU_MSMSales].[PostCodeOutward]
LEFT OUTER JOIN [MIU_AreaGroupBand] ON [MIU_MSMSales].[AreaGroupBandID] = [MIU_AreaGroupBand].[Id]
WHERE ([MSM_Sales].[PeergroupDirectOrBroker] = 'Broker' OR [MSM_Sales].[PeergroupDirectOrBroker] = 'Insurer')
AND [MSM_YourQuotes].[ProductDescription] = 'insurance'
AND FLOOR(ROUND(100.0*([MSM_YourQuotes].[YourAnnualPrice] - [MarketPriceTop5Annual]) / [MarketPriceTop5Annual], 2)) IS NOT NULL
AND [MSM_Enquiry].[EnquiryDateTime] IS NOT NULL
GROUP BY CONCAT(CONVERT(VARCHAR(8), [MSM_PolicyHolder].[DateOfBirth], 3), [MSM_PolicyHolder].[PostcodeOutward])
ORDER BY MAX([MSM_Enquiry].[EnquiryDateTime]) DESC
希望这能解释我正在努力实现的目标与我目前拥有的目标。
谢谢。
解决方案
样本数据
只是发明了一些数据。寻找一个最小的，可重现的例子。
create table client
(
clientid int,
clientname nvarchar(20),
clientdob date
);
insert into client (clientid, clientname, clientdob) values
(1, 'Clark Kent', '1938-04-18'),
(2, 'Bruce Wayne', '1939-03-30');
create table quote
(
clientid int,
quotedate date,
quoteprice money
);
insert into quote (clientid, quotedate, quoteprice) values
(1, '2021-01-01', 1000.0),
(1, '2021-02-01', 995.0),
(2, '2021-01-05', 2000.0),
(2, '2021-01-31', 1900.0);
问题再现
select c.clientid,
c.clientname,
c.clientdob,
max(q.quotedate) as quotedate, --> gives latest date :)
max(q.quoteprice) as quoteprice --> does not give latest price :(
from quote q
join client c
on c.clientId = q.clientid
group by c.clientid,
c.clientname,
c.clientdob;
这给出了：
clientid clientname clientdob quotedate quoteprice
-------- ----------- ---------- ---------- ----------
1 Clark Kent 1938-04-18 2021-02-01 1000.0000 --> not latest quote price
2 Bruce Wayne 1939-03-30 2021-01-31 2000.0000 --> not latest quote price
解决方案 1
charlieface在问题评论中建议。在公用表表达式 ( ) 中按报价日期降序对
所有行 ( ) 编号，并为每个客户获取第一行 ( )。row_number()ctecte.num = 1
with cte as
(
select c.clientid,
c.clientname,
c.clientdob,
q.quotedate,
q.quoteprice,
row_number() over(partition by c.clientid, c.clientname, c.clientdob order by q.quotedate desc) as num
from quote q
join client c
on c.clientId = q.clientid
)
select cte.clientid,
cte.clientname,
cte.clientdob,
cte.quotedate,
cte.quoteprice
from cte
where cte.num = 1;
解决方案 2
选择所有单独的客户信息（并构造客户的唯一密钥）并为每个客户获取最后一个报价信息 ( outer apply)。
select c.clientid,
c.clientname,
c.clientdob,
q1.quotedate,
q1.quoteprice
from client c
outer apply ( select top 1
q.quotedate,
q.quoteprice
from quote q
where q.clientid = c.clientid
order by q.quotedate desc ) q1;
结果
clientid clientname clientdob quotedate quoteprice
-------- ----------- ---------- ---------- ----------
1 Clark Kent 1938-04-18 2021-02-01 995.0000 --> latest quote date AND price
2 Bruce Wayne 1939-03-30 2021-01-31 1900.0000 --> latest quote date AND price
小提琴以查看实际情况。
推荐阅读
- html - 可折叠的引导程序不返回并且颜色为黑色
- sql - SQL 服务器 Where 子句变量可能为空
- python - 在 Python 中，只要任何非守护子进程正在运行，父进程是否会继续存在
- express - 仅更新 Mongoose 中更改的字段
- css - 如何使用角度 6 自动刷新表格及其单元格颜色（红色/绿色）？例如股票市场价格指数表？
- actions-on-google - 您可以在播放完成或被用户停止后使用时间码处理回调吗？
- nuxt.js - Nuxt 缓慢编译和构建
- javascript - 使用自定义 javascript 宏以 GTM 数据层格式格式化电子商务交易数据
- android - 将 ImageView 中的图像设置为 Dialog
- ruby-on-rails - 是否可以根据参数对 phusion 乘客中的请求进行负载平衡？