帮助发现子组之间的值差异. [英] Help in finding the differences in values across sub groups.

问题描述

嗨
我在编写SQL查询时需要帮助，该查询给出两个连续日期的值差异.

我的表结构如下:

Hi
I need help in writing a SQL query that gives the difference in values for two consecutive dates.

My table structure is as follows :

Symbol Name   Dates       Outstanding values
VAN Equity     12/3/2011   0.7995
VAN Equity     12/4/2011   0.7995
VAN Equity     12/5/2011   0.7000
VAN Equity     12/8/2011   0.7000

我想要以下形式的输出:

I want the output in the following form :

Symbol Name   Dates       Difference
VAN Equity     12/3/2011   0.7995
VAN Equity     12/4/2011   0
VAN Equity     12/5/2011   -0.0995
VAN Equity     12/8/2011   0

我想出了以下查询.

I came up with the below query.

WITH LHP AS
      (
      SELECT  *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn
      FROM    test_table as LI
      )
SELECT   mc.[Outstanding Values] - mp.[Outstanding Values],
        mc.[Outstanding Values] , mp.[Outstanding Values]
FROM    LHP mc
inner JOIN    LHP mp
ON    mc.rn = mp.rn - 1

如果我只有一组符号名称，则上面的查询工作正常.
但是，如果我的数据采用以下格式:

The above query works fine if I have just one set of Symbol Names.
However, if I have my data in the below format :

Symbol Name       Dates            Outstanding Values
VAN Equity   2011-12-03 00:00:00.000   0.7995
VAN Equity   2011-12-04 00:00:00.000   0.7995
VAN Equity   2011-12-05 00:00:00.000   0.7
VAN Equity   2011-12-08 00:00:00.000   0.7
VAN Equity   2011-12-09 00:00:00.000   0.6
VIN Equity   2011-12-03 00:00:00.000   0.1
VIN Equity   2011-12-04 00:00:00.000   0.2
VIN Equity   2011-12-05 00:00:00.000   0.7
VIN Equity   2011-12-08 00:00:00.000   0.7
VIN Equity   2011-12-09 00:00:00.000   0.6
VAT Equity   2011-12-03 00:00:00.000   0.1
VAT Equity   2011-12-04 00:00:00.000   0.2
VAT Equity   2011-12-05 00:00:00.000   0.7
VAT Equity   2011-12-08 00:00:00.000   0.7
VAT Equity   2011-12-09 00:00:00.000   0.6

也就是说，多组符号名称分布在同一组日期上，我的查询为我提供了如下所示的结果:这与预期不符.

i.e. multiple sets of Symbol Names distributed across the same set of dates my query gives me the results as shown below : which is not as expected.

No      Outstanding   Outstanding
Name    Values        Values
0.6995   0.7995         0.1
-0.1     0.1            0.2
-0.5995  0.2            0.7995
0.0995   0.7995         0.7
0        0.7            0.7
0.1      0.7            0.6

任何帮助将不胜感激.

Any help would be greatly appreciated.

推荐答案

[发表评论后]

更改此行:

[After your comment]

Change this line :

SELECT  *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn

为此:

To this :

SELECT  *, ROW_NUMBER() OVER(PARTITION BY [Symbol Name] ORDER BY [Symbol Name], Dates ) AS rn

应该可以解决问题.

It should solve the problem.

嗨 阿米尔，
您的解决方案不起作用，因为排名如下所示:

Hi Amir,
Your solution does not work as the ranking takes place as shown below :

VAN Equity  2011-12-03 00:00:00.000 0.7995  1
VAN Equity  2011-12-04 00:00:00.000 0.7995  2
VAN Equity  2011-12-05 00:00:00.000 0.7 3
VAN Equity  2011-12-08 00:00:00.000 0.7 4
VAN Equity  2011-12-09 00:00:00.000 0.6 5
VIN Equity  2011-12-03 00:00:00.000 0.1 1
VIN Equity  2011-12-04 00:00:00.000 0.2 2

结果，条件

as a result of which, the condition

mc.rn = mp.rn - 1

子组中的行也同样适用.

holds true for rows across the subs groups as well.

这篇关于帮助发现子组之间的值差异.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！