Вопрос по postgresql – Оконные функции и более «локальная» агрегация

7

Предположим, у меня есть эта таблица:

select * from window_test;

 k | v
---+---
 a | 1
 a | 2
 b | 3
 a | 4

В конечном итоге я хочу получить:

 k | min_v | max_v
---+-------+-------
 a | 1     | 2
 b | 3     | 3
 a | 4     | 4

Но я был бы так же счастлив получить это (так как я могу легко отфильтровать это сdistinct):

 k | min_v | max_v
---+-------+-------
 a | 1     | 2
 a | 1     | 2
 b | 3     | 3
 a | 4     | 4

Можно ли добиться этого с помощью оконных функций PostgreSQL 9.1+? Я пытаюсь понять, могу ли я заставить его использовать отдельный раздел для первого и последнего появленияk=a в этом примере (заказаноv).

Первое. Хотите ли вы «свернуть»? полные диапазоны? То есть что бы вы ожидали, когда вы добавите(a,5), (a,6), (a,7) к вашему набору данных? Второе: естьv всегда отлично увеличивается на 1 или могут быть пробелы? A.H.
@KonradGarus, работает ли какое-либо из предложенных решений для вас? vyegorov
@ Хиджры.v не совсем увеличивается (фактически это метка времени). Да, я хочу свернуть полные диапазоны и ожидать(a, 4, 5) как последний кортеж после вставки(a, 5). Konrad Garus

Ваш Ответ

3   ответа
1

EDIT: Я пришел к следующему запросу & # x2014; вообще без оконных функций:

WITH RECURSIVE tree AS (
  SELECT k, v, ''::text as next_k, 0 as next_v, 0 AS level FROM window_test
  UNION ALL
  SELECT c.k, c.v, t.k, t.v + level, t.level + 1
    FROM tree t JOIN window_test c ON c.k = t.k AND c.v + 1 = t.v),
partitions AS (
  SELECT t.k, t.v, t.next_k,
         coalesce(nullif(t.next_v, 0), t.v) AS next_v, t.level
    FROM tree t
   WHERE NOT EXISTS (SELECT 1 FROM tree WHERE next_k = t.k AND next_v = t.v))
SELECT min(k) AS k, v AS min_v, max(next_v) AS max_v
  FROM partitions p
 GROUP BY v
 ORDER BY 2;

Я предоставил 2 рабочих запроса, надеюсь, один из них вам подойдет.

SQL Fiddle для этого варианта.


Еще один способ добиться этого - использовать вспомогательную последовательность.

  1. Create a support sequence:

    CREATE SEQUENCE wt_rank START WITH 1;
    
  2. The query:

    WITH source AS (
      SELECT k, v,
             coalesce(lag(k) OVER (ORDER BY v), k) AS prev_k
        FROM window_test
        CROSS JOIN (SELECT setval('wt_rank', 1)) AS ri),
    ranking AS (
      SELECT k, v, prev_k,
             CASE WHEN k = prev_k THEN currval('wt_rank')
                  ELSE nextval('wt_rank') END AS rank
        FROM source)
    SELECT r.k, min(s.v) AS min_v, max(s.v) AS max_v
        FROM ranking r
        JOIN source s ON r.v = s.v
       GROUP BY r.rank, r.k
       ORDER BY 2;
    
Error: User Rate Limit ExceededvError: User Rate Limit Exceeded Konrad Garus
0

Разве это не сделает эту работу за вас без необходимости использования окон, перегородок или слияния. Он просто использует традиционный трюк SQL для поиска ближайших кортежей с помощью самостоятельного соединения и минимальную разницу:

SELECT k, min(v), max(v) FROM (
    SELECT k, v, v + min(d) lim FROM (
        SELECT x.*, y.k n, y.v - x.v d FROM window_test x
        LEFT JOIN window_test y ON x.k <> y.k AND y.v - x.v > 0) 
    z GROUP BY k, v, n)
w GROUP BY k, lim ORDER BY 2;

Я думаю, что это, вероятно, более «реляционный» решение, но я не уверен в его эффективности.

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededwindow_testError: User Rate Limit Exceeded<Error: User Rate Limit Exceeded>Error: User Rate Limit Exceeded
11

Это возвращает желаемый результат с примерами данных. Не уверен, будет ли это работать для данных реального мира:

select k, 
       min(v) over (partition by group_nr) as min_v,
       max(v) over (partition by group_nr) as max_v
from (
    select *,
           sum(group_flag) over (order by v,k) as group_nr
    from (
    select *,
           case
              when lag(k) over (order by v) = k then null
              else 1
            end as group_flag
    from window_test
    ) t1
) t2
order by min_v;

Я оставилDISTINCT хоть.

Error: User Rate Limit Exceeded Konrad Garus
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceededgroup by k, group_nrError: User Rate Limit Exceededorder by min_vError: User Rate Limit Exceededmin(v) over...Error: User Rate Limit Exceededmax(v) over...Error: User Rate Limit Exceededmin(v)Error: User Rate Limit Exceededmax(v)Error: User Rate Limit Exceeded

Похожие вопросы