pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: digoal@126.com
Subject: BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
Date: Fri, 23 Feb 2018 15:16:39 +0000
Message-ID: <151939899974.1461.9411971793110285476@wrigleys.postgresql.org> (raw )
The following bug has been logged on the website:
Bug reference: 15082
Logged by: Zhou Digoal
Email address: digoal@126.com
PostgreSQL version: Unsupported/Unknown
Operating system: CentOS 7.4 x64
Description:
```
create table t(
sid int, -- 学号
sub int2, -- 科目
des text, -- 科目描述
score float4 -- 分数
);
insert into t values
(1, 1, '语文', 81),
(2, 1, '语文', 71),
(3, 1, '语文', 99),
(4, 1, '语文', 100),
(5, 1, '语文', 32),
(6, 1, '语文', 89),
(7, 1, '语文', 90.5),
(1, 2, '数学', 81),
(2, 2, '数学', 96),
(3, 2, '数学', 78),
(4, 2, '数学', 90),
(5, 2, '数学', 12),
(6, 2, '数学', 97),
(7, 2, '数学', 99.5),
(1, 3, '英语', 100),
(2, 3, '英语', 95),
(3, 3, '英语', 95),
(4, 3, '英语', 95),
(5, 3, '英语', 56),
(6, 3, '英语', 87),
(7, 3, '英语', 93),
(1, 4, '物理', 60),
(2, 4, '物理', 60),
(3, 4, '物理', 65),
(4, 4, '物理', 65),
(5, 4, '物理', 65),
(6, 4, '物理', 71),
(7, 4, '物理', 71)
;
select
*,
sum(score) over w1,
avg(score) over w1,
first_value(score) over w1,
last_value(score) over w1
from t
window w1 as (partition by sub order by score::int range between 1
preceding and 6 following)
order by sub, score;
sid | sub | des | score | sum | avg | first_value |
last_value
-----+-----+------+-------+-----+---------------------+-------------+------------
5 | 1 | 语文 | 32 | 562 | 80.2857142857142857 | 32 |
100
2 | 1 | 语文 | 71 | 562 | 80.2857142857142857 | 32 |
100
1 | 1 | 语文 | 81 | 562 | 80.2857142857142857 | 32 |
100
6 | 1 | 语文 | 89 | 562 | 80.2857142857142857 | 32 |
100
7 | 1 | 语文 | 90 | 562 | 80.2857142857142857 | 32 |
100
3 | 1 | 语文 | 99 | 562 | 80.2857142857142857 | 32 |
100
4 | 1 | 语文 | 100 | 562 | 80.2857142857142857 | 32 |
100
5 | 2 | 数学 | 12 | 554 | 79.1428571428571429 | 12 |
100
3 | 2 | 数学 | 78 | 554 | 79.1428571428571429 | 12 |
100
1 | 2 | 数学 | 81 | 554 | 79.1428571428571429 | 12 |
100
4 | 2 | 数学 | 90 | 554 | 79.1428571428571429 | 12 |
100
2 | 2 | 数学 | 96 | 554 | 79.1428571428571429 | 12 |
100
6 | 2 | 数学 | 97 | 554 | 79.1428571428571429 | 12 |
100
7 | 2 | 数学 | 100 | 554 | 79.1428571428571429 | 12 |
100
5 | 3 | 英语 | 56 | 621 | 88.7142857142857143 | 56 |
100
6 | 3 | 英语 | 87 | 621 | 88.7142857142857143 | 56 |
100
7 | 3 | 英语 | 93 | 621 | 88.7142857142857143 | 56 |
100
3 | 3 | 英语 | 95 | 621 | 88.7142857142857143 | 56 |
100
4 | 3 | 英语 | 95 | 621 | 88.7142857142857143 | 56 |
100
2 | 3 | 英语 | 95 | 621 | 88.7142857142857143 | 56 |
100
1 | 3 | 英语 | 100 | 621 | 88.7142857142857143 | 56 |
100
1 | 4 | 物理 | 60 | 457 | 65.2857142857142857 | 60 |
71
2 | 4 | 物理 | 60 | 457 | 65.2857142857142857 | 60 |
71
3 | 4 | 物理 | 65 | 457 | 65.2857142857142857 | 60 |
71
4 | 4 | 物理 | 65 | 457 | 65.2857142857142857 | 60 |
71
5 | 4 | 物理 | 65 | 457 | 65.2857142857142857 | 60 |
71
6 | 4 | 物理 | 71 | 457 | 65.2857142857142857 | 60 |
71
7 | 4 | 物理 | 71 | 457 | 65.2857142857142857 | 60 |
71
(28 rows)
select
*,
sum(score) over w1,
avg(score) over w1,
first_value(score) over w1,
last_value(score) over w1
from t
window w1 as (order by score::int range between 1 preceding and 6
following)
order by score;
sid | sub | des | score | sum | avg | first_value |
last_value
-----+-----+------+-------+-----+---------------------+-------------+------------
5 | 2 | 数学 | 12 | 12 | 12.0000000000000000 | 12 |
12
5 | 1 | 语文 | 32 | 32 | 32.0000000000000000 | 32 |
32
5 | 3 | 英语 | 56 | 176 | 58.6666666666666667 | 56 |
60
1 | 4 | 物理 | 60 | 315 | 63.0000000000000000 | 60 |
65
2 | 4 | 物理 | 60 | 315 | 63.0000000000000000 | 60 |
65
3 | 4 | 物理 | 65 | 408 | 68.0000000000000000 | 65 |
71
4 | 4 | 物理 | 65 | 408 | 68.0000000000000000 | 65 |
71
5 | 4 | 物理 | 65 | 408 | 68.0000000000000000 | 65 |
71
6 | 4 | 物理 | 71 | 213 | 71.0000000000000000 | 71 |
71
7 | 4 | 物理 | 71 | 213 | 71.0000000000000000 | 71 |
71
2 | 1 | 语文 | 71 | 213 | 71.0000000000000000 | 71 |
71
3 | 2 | 数学 | 78 | 240 | 80.0000000000000000 | 78 |
81
1 | 1 | 语文 | 81 | 249 | 83.0000000000000000 | 81 |
87
1 | 2 | 数学 | 81 | 249 | 83.0000000000000000 | 81 |
87
6 | 3 | 英语 | 87 | 449 | 89.8000000000000000 | 87 |
93
6 | 1 | 语文 | 89 | 647 | 92.4285714285714286 | 89 |
95
7 | 1 | 语文 | 90 | 743 | 92.8750000000000000 | 89 |
96
4 | 2 | 数学 | 90 | 743 | 92.8750000000000000 | 89 |
96
7 | 3 | 英语 | 93 | 670 | 95.7142857142857143 | 93 |
99
3 | 3 | 英语 | 95 | 877 | 97.4444444444444444 | 95 |
100
2 | 3 | 英语 | 95 | 877 | 97.4444444444444444 | 95 |
100
4 | 3 | 英语 | 95 | 877 | 97.4444444444444444 | 95 |
100
2 | 2 | 数学 | 96 | 877 | 97.4444444444444444 | 95 |
100
6 | 2 | 数学 | 97 | 592 | 98.6666666666666667 | 96 |
100
3 | 1 | 语文 | 99 | 399 | 99.7500000000000000 | 99 |
100
7 | 2 | 数学 | 100 | 399 | 99.7500000000000000 | 99 |
100
4 | 1 | 语文 | 100 | 399 | 99.7500000000000000 | 99 |
100
1 | 3 | 英语 | 100 | 399 | 99.7500000000000000 | 99 |
100
(28 rows)
```
when i use frame clause(range), it's only right when don't use partition by,
and use the whole rows as one partition.
is it a bug?
digoal,
best regards.
view thread (2+ messages) latest in thread
Message-ID: <151939899974.1461.9411971793110285476@wrigleys.postgresql.org>
Permalink: ../151939899974.1461.9411971793110285476@wrigleys.postgresql.org/
Also on: postgresql.org/message-id/151939899974.1461.9411971793110285476@wrigleys.postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, digoal@126.com
Subject: Re: BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
In-Reply-To: <151939899974.1461.9411971793110285476@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox