最大值和最小值在同一列的值之间的差异?

Help with MS SQL query needed. I need to display the difference between two values in the same column where data is between max and min date. For example if I have the following table:

帮助用户查询SQL语句。我需要在同一列中显示两个值之间的差异，其中数据在max和min日期之间。例如，如果我有以下表格:

see details behind the image

查看图片背后的细节。

location|   date        |amount
------- |   -------     |-------
1000    |   2016-01-01  |400
1000    |   2016-01-04  |600    
1000    |   2016-01-05  |650
1000    |   2016-01-06  |820
1000    |   2016-01-18  |850
1000    |   2016-01-22  |900
1000    |   2016-01-28  |910
1000    |   2016-01-31  |950
2000    |   2016-01-07  |100
2000    |   2016-01-13  |150
2000    |   2016-01-15  |260
2000    |   2016-01-20  |330

The output should be as follow:

输出应如下:

location|   difference
------- |   -------
1000    |   550
2000    |   230

Thanks in advance.

提前谢谢。

6 个解决方案

#1

You can as the below:

你可以如下所示:

SELECT
   location,
   MAX(Amount) - MIN(Amount) difference
FROM
  Tbl
GROUP BY
  location

Updated

更新

SELECT
    T.location,
    Maxv.Amount - Minv.Amount as difference
FROM
(
    SELECT
       location,
       MIN(date) MinDate,
       MAX(date) MaxDate
    FROM
      Tbl A 
    GROUP BY
      location
) T INNER JOIN 
Tbl Minv ON T.MinDate = Minv.date AND T.location = Minv.location INNER JOIN 
Tbl Maxv ON T.MaxDate= Maxv.date AND T.location = Maxv.location

#2

It is a classic top-n-per-group problem, where you need to get not just top, but also bottom value per group.

这是一个典型的每个组的上n值问题，在这个问题中，您不仅需要得到顶部，还需要得到每个组的下值。

One standard way to achieve it without self-joins is by using ROW_NUMBER. In your case there will be two sets of numbers: ordered ascending and descending.

不使用自连接实现它的一种标准方法是使用ROW_NUMBER。在您的例子中，将有两组数字:有序升序和降序。

Sample data

样本数据

DECLARE @T TABLE (location int, dt date, Amount int);
INSERT INTO @T (location, dt, Amount ) VALUES
(1000,'2016-01-01',400),
(1000,'2016-01-04',600),
(1000,'2016-01-05',650),
(1000,'2016-01-06',820),
(1000,'2016-01-18',850),
(1000,'2016-01-22',900),
(1000,'2016-01-28',910),
(1000,'2016-01-31',950),
(2000,'2016-01-07',100),
(2000,'2016-01-13',150),
(2000,'2016-01-15',260),
(2000,'2016-01-20',330);

Query

查询

WITH
CTE
AS
(
    SELECT
        location
        ,dt
        ,Amount
        ,ROW_NUMBER() OVER (PARTITION BY location ORDER BY dt ASC) AS rnAsc
        ,ROW_NUMBER() OVER (PARTITION BY location ORDER BY dt DESC) AS rnDesc
    FROM @T
)
SELECT
    location
    ,SUM(CASE WHEN rnAsc = 1 THEN -Amount ELSE Amount END) AS diff
FROM CTE
WHERE
    rnAsc = 1
    OR rnDesc = 1
GROUP BY location
ORDER BY location;

Result

结果

+----------+------+
| location | diff |
+----------+------+
|     1000 |  550 |
|     2000 |  230 |
+----------+------+

To understand how it works run just the query inside the CTE at first and examine the intermediate results.

要理解它是如何工作的，首先在CTE内部运行查询并检查中间结果。

The main query simply groups by location and calculates the SUM of two values. CASE expression changes the sign of the first value to get the difference.

主查询简单地按位置分组并计算两个值的和。CASE表达式更改第一个值的符号以获得差异。

If it is possible to have a location with just one row, the result would be negative Amount. As if it is a difference between 0 and the only available Amount value. If you want to get some other result in this case, please clarify the question.

如果可能有一个只有一行的位置，那么结果将是负数。就好像它是0和唯一可用金额之间的差值。如果你想在这种情况下得到其他的结果，请澄清这个问题。

#3

BEGIN TRAN

CREATE TABLE #Details(_Location VARCHAR(100),_Date DATE,Amount INT)
CREATE TABLE #Diff(Location VARCHAR(100),_MinDate DATE,_MaxDate DATE,Amount1 INT,Amount2 INT)

INSERT INTO #Details(_Location ,_Date ,Amount )
SELECT 1000,'2016-01-01',400 UNION ALL
SELECT 1000,'2016-01-04',600 UNION ALL
SELECT 1000,'2016-01-05',650 UNION ALL
SELECT 1000,'2016-01-06',820 UNION ALL
SELECT 1000,'2016-01-18',850 UNION ALL
SELECT 1000,'2016-01-22',900 UNION ALL
SELECT 1000,'2016-01-28',910 UNION ALL
SELECT 1000,'2016-01-31',950 UNION ALL
SELECT 2000,'2016-01-07',100 UNION ALL
SELECT 2000,'2016-01-13',150 UNION ALL
SELECT 2000,'2016-01-15',260 UNION ALL
SELECT 2000,'2016-01-20',330 


INSERT INTO  #Diff (Location ,_MinDate ,_MaxDate)
SELECT _Location , MIN(_Date) _MinDate, MAX(_Date) _MaxDate
FROM #Details
GROUP BY _Location


UPDATE #Diff SET Amount1 = Amount
FROM #Details
WHERE _Date = _MaxDate AND _Location = Location

UPDATE #Diff SET Amount2 = Amount
FROM #Details
WHERE _Date = _MinDate AND _Location = Location

SELECT Location , ( Amount1 - Amount2 ) [Difference]
FROM #Diff

ROLLBACK TRAN

#4

Try This:

试试这个:

Easy & Short Query:

简单和短的查询:

DECLARE @loc_tbl TABLE( location INT, loc_date DATE,amount INT)
INSERT INTO @loc_tbl VALUES('1000','2016-01-01','400'),
('1000','2016-01-04','600'),('1000','2016-01-05','650'),
('1000','2016-01-06','820'),('1000','2016-01-18','850'),
('1000','2016-01-22','900'),('1000','2016-01-28','910'),
('1000','2016-01-31','950'),('2000','2016-01-07','100'),
('2000','2016-01-13','150'),('2000','2016-01-15','260'),
('2000','2016-01-20','330')

SELECT A.location,(B.amount-C.amount) amt_difference FROM ( SELECT location,
MAX(loc_Date) maximum,MIN(loc_Date) minimum FROM @loc_tbl GROUP BY location ) A
LEFT JOIN @loc_tbl B ON B.loc_date = A.maximum
LEFT JOIN @loc_tbl C ON C.loc_date = A.minimum

#5

Below is the Table(Name: MyTab)

下面是表(Name: MyTab)

  Location  dt      amount
    1000    2014-06-01  90
    1000    2014-09-01  40
    1000    2014-10-01  100
    1000    2015-06-01  900
    1000    2015-12-01  907
    2000    2004-11-22  432
    2000    2010-11-22  709
    2000    2014-11-01  922
    2000    2014-11-22  728
    2000    2015-11-01  907
    2000    2015-12-01  907
    2000    2016-11-22  553

Below is the Query

下面是查询

;WITH CTE
AS(
SELECT location,min(dt) mindt,max(dt) maxdt from MyTab group by location
)
SELECT c.location,max(m.amount)-min(m.amount) amt from cte c 
INNER JOIN MyTab m on c.location = m.location and (c.mindt = m.dt or c.maxdt = m.dt )
GROUP BY c.location

Output is

输出是

location amt
1000    817
2000    121

#6

DECLARE @T TABLE (location int, dt date, Amount int);
INSERT INTO @T (location, dt, Amount ) VALUES
(1000,'2016-01-01',400),
(1000,'2016-01-04',600),
(1000,'2016-01-05',650),
(1000,'2016-01-06',820),
(1000,'2016-01-18',850),
(1000,'2016-01-22',900),
(1000,'2016-01-28',910),
(1000,'2016-01-31',950),
(2000,'2016-01-07',100),
(2000,'2016-01-13',150),
(2000,'2016-01-15',260),
(2000,'2016-01-20',540);


 with cte as (select location,first_value (amount)OVER(partition by location order by dt desc)v1
,last_value(amount) OVER (partition by location order by (select NULL))as v2
from @T
)
select distinct(location),v1-v2 from cte

#1