代码之家 › 专栏 › 技术社区 › J. Michiels

SQL:达到总和阈值后显示记录

sql-server sql

J. Michiels · 技术社区 · 6 年前

我有一个表,按日期值(ASC)排序。

+----+------------+-------+
| Id |    Date    | Value |
+----+------------+-------+
|  1 | 2018-01-01 |    10 |
|  2 | 2018-01-02 |     5 |
|  3 | 2018-01-03 |    15 |
|  4 | 2018-01-04 |     0 |
|  5 | 2018-01-05 |     5 |
|  6 | 2018-01-06 |    10 |
|  7 | 2018-01-07 |     5 |
|  8 | 2018-01-08 |     0 |
|  9 | 2018-01-09 |     0 |
| 10 | 2018-01-10 |    10 |
+----+------------+-------+

我想创建一个视图,它只在值的和大于30时返回记录,从第一个记录开始。所以我的阈值是30,每个值符合前30的记录都应该被隐藏。一旦达到此阈值,所有遵循的记录都需要显示。

这意味着我需要的结果如下:

+----+------------+-------+
| Id |    Date    | Value |
+----+------------+-------+
|  4 | 2018-01-04 |     0 |
|  5 | 2018-01-05 |     5 |
|  6 | 2018-01-06 |    10 |
|  7 | 2018-01-07 |     5 |
|  8 | 2018-01-08 |     0 |
|  9 | 2018-01-09 |     0 |
| 10 | 2018-01-10 |    10 |
+----+------------+-------+

如您所见,Id的1、2和3被省略了,因为它们的值(10、5和15)加起来是30。一旦达到此阈值,其余记录将可见(甚至Id 4的0值)。

我创建了一些脚本来设置包含数据的测试表:

-- Create test table
CREATE TABLE thresholdTest (
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [Date] DATE NOT NULL,
    [Value] INT NOT NULL
)

-- Insert dummies
INSERT INTO [thresholdTest] ([Date],[Value])
VALUES
('2018-01-01',10),
('2018-01-02',5),
('2018-01-03',15),
('2018-01-04',0),
('2018-01-05',5),
('2018-01-06',10),
('2018-01-07',5),
('2018-01-08',0),
('2018-01-09',0),
('2018-01-10',10);

-- Select ordered by date
SELECT *
FROM [thresholdTest]
ORDER BY [Date] ASC

我只需要一个SELECT语句/视图。阈值始终是静态的(在本例中为30)。数据当然可能不同,但总是按日期排序并包含一个值。

提前谢谢你。

3 回复 | 直到 6 年前

S3S 6 年前

我会用一个窗口函数:

;with cte as(
select *,  tot = sum([Value]) over (order by [Date])
from thresholdTest
)

select 
   Id, 
   [Date], 
   [Value]
from cte
where 
    (tot >= 30 and [Value] = 0)
    or tot > 30

D-Shih 6 年前

你可以试着用 SUM 具有 窗口函数 在子查询中累积 totle 然后在主查询中写入条件。

select Id,
      Date,
      Value
from 
(
  SELECT *,
         SUM(Value) OVER(ORDER BY Date) totle
  FROM thresholdTest
) t
WHERE totle > 30 OR (Value = 0 AND totle = 30)

[结果] :

| Id |       Date | Value |
|----|------------|-------|
|  4 | 2018-01-04 |     0 |
|  5 | 2018-01-05 |     5 |
|  6 | 2018-01-06 |    10 |
|  7 | 2018-01-07 |     5 |
|  8 | 2018-01-08 |     0 |
|  9 | 2018-01-09 |     0 |
| 10 | 2018-01-10 |    10 |

sqlfiddle

saul 6 年前

另一种方法

select t1.id, t1.Date,t1.Value
from [thresholdTest] t1
inner join [thresholdTest] t2 on t1.id >= t2.id
group by t1.id, t1.value, t1.Date
HAVING SUM(t2.VAlue)>30 OR( SUM(t2.value)=30 AND t1.value=0)