ROW_NUMBER()显示意外值 [英] ROW_NUMBER() shows unexpected values
问题描述
我的表的值类似( RowCount
由下面的查询生成):
My table has values like (RowCount
is generated by the query below):
ID Date_trans Time_trans Price RowCount
------- ----------- ---------- ----- --------
1699093 22-Feb-2011 09:30:00 58.07 1
1699094 22-Feb-2011 09:30:00 58.08 1
1699095 22-Feb-2011 09:30:00 58.08 2
1699096 22-Feb-2011 09:30:00 58.08 3
1699097 22-Feb-2011 09:30:00 58.13 1
1699098 22-Feb-2011 09:30:00 58.13 2
1699099 22-Feb-2011 09:30:00 58.12 1
1699100 22-Feb-2011 09:30:08 58.13 3
1699101 22-Feb-2011 09:30:09 57.96 1
1699102 22-Feb-2011 09:30:09 57.95 1
1699103 22-Feb-2011 09:30:09 57.93 1
1699104 22-Feb-2011 09:30:09 57.96 2
1699105 22-Feb-2011 09:30:09 57.93 2
1699106 22-Feb-2011 09:30:09 57.93 3
1699107 22-Feb-2011 09:30:37 58 1
1699108 22-Feb-2011 09:30:37 58.08 4
1699109 22-Feb-2011 09:30:38 58.08 5
1699110 22-Feb-2011 09:30:41 58.02 1
1699111 22-Feb-2011 09:30:41 58.02 2
1699112 22-Feb-2011 09:30:41 58.01 1
1699113 22-Feb-2011 09:30:41 58.01 2
1699114 22-Feb-2011 09:30:41 58.01 3
1699115 22-Feb-2011 09:30:42 58.02 3
1699116 22-Feb-2011 09:30:42 58.02 4
1699117 22-Feb-2011 09:30:45 58.04 1
1699118 22-Feb-2011 09:30:54 58 2
1699119 22-Feb-2011 09:30:57 58.05 1
ID
列是IDENTITY列。
我正在使用此查询来获取连续行计数为:
The ID
column is an IDENTITY column.
And I'm using this query to get the consecutive row count as:
SELECT ID, Date_trans, Time_trans, Price
,ROW_NUMBER() OVER(PARTITION BY Price ORDER BY ID) RowCount
FROM MyTable
ORDER BY ID;
我得到的 RowCount
最适合值,但某些值是错误的。例如:
The RowCount
I get is right for most of the values but wrong for some values. For instance:
- ID 1699100价格58.13 –计数应为1(显示3)。
- ID 1699104价格57.96 –计数应为1(显示2)。
- ID 1699105,1699106价格57.93 –计数应为1、2(显示2、3)。
我在PostgreSQL中尝试了相同的查询并找到了相同的结果。
我上传了此处是一个csv数据示例。
I have tried the same query in PostgreSQL and found the same results.
I have uploaded a csv data sample here.
我对这种意外的分区结果感到困惑。有人可以帮我吗?
I'm stuck with such unexpected results of partition. Can anybody help me?
推荐答案
PARTITION BY
子句code> ROW_NUMBER()函数指示它按 Price
值设置的 entire 行分区并分配行数字以 ID
s的升序排列。
The PARTITION BY
clause of the ROW_NUMBER()
function instructs it to partition the entire row set by Price
values and assign row numbers in the ascending order of ID
s.
似乎您想区分任意两组行具有相同 Price
值的值,这些值由至少一行用不同的 Price
分隔。
It seems like you want to distinguish between any two groups of rows with identical Price
values that are separated by at least one row with a different Price
.
可能有多种方法可以实现。在SQL Server中(我认为同样适用于PostgreSQL),我将首先使用两个 ROW_NUMBER()
调用来获取其他分区条件,然后再次对行进行排名使用该标准,例如:
There may be various ways to achieve that. In SQL Server (and I think the same would work in PostgreSQL too), I would first use two ROW_NUMBER()
calls to get an additional partitioning criterion, then rank rows once again using that criterion, like this:
WITH partitioned AS (
SELECT
ID,
Date_trans,
Time_trans,
Price,
ROW_NUMBER() OVER ( ORDER BY ID) -
ROW_NUMBER() OVER (PARTITION BY Price ORDER BY ID) AS PriceGroup
FROM MyTable
)
SELECT
ID,
Date_trans,
Time_trans,
Price,
ROW_NUMBER() OVER (PARTITION BY Price, PriceGroup ORDER BY ID) AS RowCount
FROM partitioned
ORDER BY ID
;
这里是 SQL Fiddle演示。
这篇关于ROW_NUMBER()显示意外值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!