时间戳

首页 > 解决方案 > 使用此表结构为每个唯一目标提取最新目标值的最佳方法？

sql - 使用此表结构为每个唯一目标提取最新目标值的最佳方法？

问题描述

所以我有一个类似于这个结构的表：

目标ID 目标名称 目标类型 使用时间范围 更改时间范围 目标上限 目标下界 目标价值 生效日期 结束日期
1 销售量 单一目标 每年 每年 无效的 无效的 5,000,000 01-01-2021 2021 年 12 月 31 日
2 独特的购买者 范围 月刊 月刊 22,000 20,000 无效的 2021 年 9 月 1 日 2021 年 9 月 30 日
3 销售量 单一目标 月刊 每年 无效的 无效的 500,000 2021 年 10 月 1 日 2021 年 10 月 31 日
4 独特的购买者 范围 月刊 月刊 24,000 21,000 无效的 2021 年 10 月 1 日 2021 年 10 月 31 日
5 新客户 单一目标 月刊 每年 无效的 无效的 5,000 01-01-2021 2021 年 12 月 31 日
6 售出的产品 范围 月刊 月刊 10,000 9,000 无效的 2021 年 10 月 1 日 2021 年 10 月 31 日
7 售出的产品 范围 月刊 月刊 12,000 10,000 无效的 2021 年 10 月 15 日 2021 年 10 月 31 日
8 销售量 单一目标 每年 每年 无效的 无效的 4,750,000 01-01-2020 2020 年 12 月 31 日
9 推荐人 单一目标 月刊 每年 无效的 无效的 1,000 01-01-2021 2021 年 12 月 31 日

提取每个唯一 GoalName 及其对应的 GoalUpperBound/GoalLowerBound 或 GoalValue 的最佳方式是什么？所以在这个例子中，如果我想提取所有每月变化的目标，我想看到 GoalIDs (3, 4, 5, 7, 9)，如果我要提取每年改变的目标，我会看到 GoalID ( 1）。我正在考虑为每个 DISTINCT GoalName 和 UsedTimeframe 提取 MAX(GoalID)，然后将 GoalID 加入到该表中，或者可能是 MAX(EffectiveDate)？

标签： sqlsql-servertsql

解决方案

您可以使用窗口函数，特别ROW_NUMBER()是对组进行编号GoalID DESC- 然后您只需要 a ROW_NUMBER()= 1 的组。由于您不能在WHERE子句中使用窗口函数，因此您必须将其编写为子查询。

为了测试这一点，我创建了一个表变量并将您的数据放入其中：

DECLARE @Goals AS TABLE (
    GoalID INT NOT NULL, 
    GoalName VARCHAR(20) NOT NULL, 
    GoalType VARCHAR(20) NOT NULL, 
    UsedTimeframe VARCHAR(20) NOT NULL, 
    ChangedTimeframe VARCHAR(20) NOT NULL,
    GoalUpperBound INT NULL,
    GoalLowerBound INT NULL,
    GoalValue INT NULL,
    EffectiveDate DATE NOT NULL,
    EndDate DATE NOT NULL
);

INSERT INTO @Goals(GoalID,GoalName,GoalType,UsedTimeframe,ChangedTimeframe,GoalUpperBound,GoalLowerBound,GoalValue,EffectiveDate,EndDate)
VALUES 
(1,'Sales','Single Target','Annually','Annually',NULL,NULL,5000000,'01-01-2021','12-31-2021'),
(2,'Unique Purchasers','Range','Monthly','Monthly',22000,20000,NULL,'9-01-2021','9-30-2021'),
(3,'Sales','Single Target','Monthly','Annually',NULL,NULL,500000,'10-01-2021','10-31-2021'),
(4,'Unique Purchasers','Range','Monthly','Monthly',24000,21000,NULL,'10-01-2021','10-31-2021'),
(5,'New Customers','Single Target','Monthly','Annually',NULL,NULL,5000,'01-01-2021','12-31-2021'),
(6,'Products Sold','Range','Monthly','Monthly',10000,9000,NULL,'10-01-2021','10-31-2021'),
(7,'Products Sold','Range','Monthly','Monthly',12000,10000,NULL,'10-15-2021','10-31-2021'),
(8,'Sales','Single Target','Annually','Annually',NULL,NULL,4750000,'01-01-2020','12-31-2020'),
(9,'Referrals','Single Target','Monthly','Annually',NULL,NULL,1000,'01-01-2021','12-31-2021');

那么查询是：

SELECT GoalID,GoalName,GoalType,UsedTimeframe,ChangedTimeframe,GoalUpperBound,GoalLowerBound,GoalValue,EffectiveDate,EndDate 
FROM 
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY GoalName ORDER BY GoalID DESC) AS rn
    FROM @Goals 
    WHERE UsedTimeframe = 'Monthly' 
) AS orderedSubQuery 
WHERE rn = 1 
ORDER BY GoalID

这将按照您的要求返回目标 3、4、5、7、9。

推荐阅读