如何对 SQL 中的连续值进行分组 [英] How to group by on consecutive values in SQL

问题描述

我在 SQL Server 2014 中有一个表，其中包含如下示例数据.

I have a table in SQL Server 2014 with sample data as follows.

WK_NUM | NET_SPRD_LCL 
10       0 
11       1500 
12       3600 
13       3800 
14       4000

我想在工作中编写一个奖金结构，我需要在 WK_NUM 上分组.所以，如果我看到 NET_SPRD_LCL > 3500 for 连续两个 WK_NUMs WHERE WK_NUM 27，我需要输出2000.在这个例子中，由于WK_NUM 12和13的NET_SPRD_LCL都大于3500，所以SQL应该输出2000并退出.所以，应该忽略WK_NUM 13和14也满足NET_SPRD_LCL > 3500的条件.

I am trying to code a bonus structure at work where I need to group on WK_NUM. So, if I see NET_SPRD_LCL > 3500 for two consecutive WK_NUMs WHERE WK_NUM < 27, I need to output 2000. In this example, since NET_SPRD_LCL for WK_NUM 12 and 13 are both greater than 3500, the SQL should output 2000 and exit. So, it should ignore the fact that WK_NUM 13 and 14 also satisfy the condition that NET_SPRD_LCL > 3500.

我将不胜感激.

推荐答案

首先，当你说你想要你的查询输出"和退出"时，这让我觉得你正在接近 t-sql 作为一个过程语言，它不是.好的 t-sql 查询几乎总是基于设置的.

First of all, when you say you want your query to 'output' and 'exit', it makes me think you are approaching t-sql as a procedural language, which it is not. Good t-sql queries are nearly always set based.

无论如何，在查询之前，让我添加一些有助于其他人使用数据构建查询的内容:

In any case, before the query, let me add what is helpful for others to work with the data to build queries:

DECLARE @t TABLE (WK_NUM INT, NET_SPRD_LCL INT);
INSERT INTO @t VALUES 
(10, 0),
(11, 1500),
(12, 3600),
(13, 3800),
(14, 4000);

您说您使用的是 SQL Server 2014，这意味着您可以使用相关的窗口函数.我正在使用的 (LAG) 将具有优于使用子查询的性能，如果您坚持使用，可以通过将 TOP (1) 与 ORDER BY 和适当的索引一起使用，而不是在整体上使用 MIN 函数来大大提高性能数据集.使用少量数据，您不会注意到差异，但在实际业务系统中，这将是显而易见的.

You say you are using SQL Server 2014, which means you have relevant window functions at your disposal. The one I am using (LAG) will have superior performance to using subqueries, which, if you insist on using, can be greatly improved by using TOP (1) with ORDER BY and an appropriate index instead of using a MIN function over the whole dataset. With tiny amounts of data you won't notice a difference, but on a real business system it will be obvious.

在 OP 澄清后调整为在正确的行上提供 2000 奖金:

Adjusted to provide the 2000 bonus on the correct line after OP's clarification:

WITH cteTemp AS
    (
    SELECT  WK_NUM
        ,   thisValue = NET_SPRD_LCL
        ,   lastValue = LAG(NET_SPRD_LCL) OVER(ORDER BY WK_NUM)
    FROM @t
    WHERE WK_NUM < 27
    )
, cteBonusWeek AS
    (
    SELECT  TOP (1)
            WK_NUM
        ,   bonus = 2000
    FROM cteTemp
    WHERE thisValue > 3500 AND lastValue > 3500
    ORDER BY WK_NUM
    )
SELECT  t.WK_NUM
    ,   t.NET_SPRD_LCL
    ,   bonus = COALESCE(b.bonus, 0)
FROM @t AS t
LEFT JOIN cteBonusWeek AS b
    ON b.WK_NUM = t.WK_NUM;

这篇关于如何对 SQL 中的连续值进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！