| From: | Jacob Champion <jacob(dot)champion(at)enterprisedb(dot)com> |
|---|---|
| To: | Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> |
| Cc: | vik(at)postgresfriends(dot)org, pgsql-hackers(at)postgresql(dot)org, er(at)xs4all(dot)nl, peter(at)eisentraut(dot)org |
| Subject: | Re: Row pattern recognition |
| Date: | 2024-04-24 17:55:29 |
| Message-ID: | CAOYmi+nZfJfF_Ggnc=Dz9YjA_e14y=EArzT1Jaoj2n+YRrs8EQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Tue, Apr 23, 2024 at 8:13 PM Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:
> SELECT v.a, count(*) OVER w
> FROM (VALUES ('A'),('B'),('B'),('C')) AS v (a)
> WINDOW w AS (
> ORDER BY v.a
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN (B+)
> DEFINE B AS a = 'B'
> )
> a | count
> ---+-------
> A | 0
> B | 2
> B |
> C | 0
> (4 rows)
>
> Here row 3 is skipped because the pattern B matches row 2 and 3. In
> this case I think cont(*) should return 0 rathern than NULL for row 3.
I think returning zero would match Vik's explanation upthread [1],
yes. Unfortunately I don't have a spec handy to double-check for
myself right now.
--Jacob
[1] https://www.postgresql.org/message-id/c9ebc3d0-c3d1-e8eb-4a57-0ec099cbda17%40postgresfriends.org
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Robert Haas | 2024-04-24 17:59:33 | some additional (small) problems with pg_combinebackup and tablespaces |
| Previous Message | Alvaro Herrera | 2024-04-24 17:36:17 | Re: cataloguing NOT NULL constraints |