윈도우 함수 또는 공통 테이블 표현식 : 범위 내에서 이전 행을 계산

나는 각 행, 특정 기준을 충족 이전 레코드의 총 수를 결정하기 위해 윈도우 함수를 사용하고 싶습니다.

특정 예 :

clone=# \d test
              Table "pg_temp_2.test"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 id     | bigint                      | 
 date   | timestamp without time zone |

나는 각 날짜에 대해 해당 날짜에 '이전 1시간'내 행의 수를 알고 싶습니다.

나는 창 기능이 작업을 수행 할 수 있습니까? 아니면 내가 CTE의 조사를해야합니까?

난 정말 (작동하지 않는) 같은 것을 쓸 수 있어야합니다 :

SELECT id, date, count(*) OVER (HAVING previous_rows.date >= (date - '1 hour'::interval))
FROM test;

나는 아래로, 그 자체에 대한 테스트를 결합하여 쓸 수 있습니다 -하지만 특히 대형 테이블로 확장되지 않습니다.

SELECT a.id, a.date, count(b.*)-1 
FROM test a, test b 
WHERE (b.date >= a.date - '1 hour'::interval AND b.date < a.date)
GROUP BY 1,2
ORDER BY 2;

이것은 내가 재귀 쿼리와 함께 할 수있는 일입니까? 아니면 일반 CTE? CTE는 내가 아직 대해 전체를 많이 알고있는 없습니다. 내가 곧 갈거야 느낌이 듭니다. :)

해결법

==============================
1.자신의 프레임 정의는 정적이지만 동적 프레임이 필요합니다 - 당신이 일반 쿼리, 열팽창 계수 및 창 기능이 싸게 할 수 있다고 생각하지 않습니다.

자신의 프레임 정의는 정적이지만 동적 프레임이 필요합니다 - 당신이 일반 쿼리, 열팽창 계수 및 창 기능이 싸게 할 수 있다고 생각하지 않습니다.

일반적으로, 당신은 신중하게 윈도우의 하부 및 상부 경계를 정의해야합니다 다음 쿼리는 현재 행을 제외하고 아래쪽 테두리를 포함한다. 사소한 차이가 여전히 존재 : 함수가 현재 행의 이전 동료를 포함, 상관 하위 쿼리 제외 그 동안 ...

TS 대신 열 이름과 예약어 날짜를 사용.
```
CREATE TEMP TABLE test (
  id  bigint
 ,ts  timestamp
);
```
사용 CTE를 배열로 집계 타임 스탬프, unnest, 계산 ... 올바른 반면, 성능은 행의 전체 손보다 더으로 크게 떨어집니다. 여기에 성능 살인자의 몇 가지 있습니다. 아래를 참조하십시오.

나는 로마의 쿼리를 가져다가 그것에게 조금을 합리화하려고 : - 필요가 없습니다 2 CTE를 제거합니다. - 빠른있는 하위 쿼리에 1 CTE를 변환. - 직접 카운트 () 대신 array_length와 어레이 계수로 재 - 응집 ().

그러나 배열 처리는 고가이며, 성능은 여전히 더 행이 심하게 저하.
```
SELECT id, ts
      ,(SELECT count(*)::int - 1
        FROM   unnest(dates) x
        WHERE  x >= sub.ts - interval '1h') AS ct
FROM (
   SELECT id, ts
         ,array_agg(ts) OVER(ORDER BY ts) AS dates
   FROM   test
   ) sub;
```
당신은 평범하고 단순하게, 추한 상관 하위 쿼리와 함께이 문제를 해결 할 수있다. 훨씬 빨리,하지만 여전히 ...
```
SELECT id, ts
      ,(SELECT count(*)
        FROM   test t1
        WHERE  t1.ts >= t.ts - interval '1h'
        AND    t1.ts < t.ts) AS ct
FROM   test t
ORDER  BY ts;
```
plpgsql 기능에 ROW_NUMBER ()와 시간 순서대로 행을 통해 루프와 결합이 원하는 기간에 걸쳐 동일한 쿼리를 통해 커서와. 그리고 우리는 단지 행 번호를 뺄 수 있습니다. 잘 수행해야합니다.
```
CREATE OR REPLACE FUNCTION running_window_ct()
  RETURNS TABLE (id bigint, ts timestamp, ct int) AS
$func$
DECLARE
   i   CONSTANT interval = '1h';  -- given interval for time frame
   cur CURSOR FOR
       SELECT t.ts + i AS ts1     -- incremented by given interval
            , row_number() OVER (ORDER BY t.ts) AS rn
       FROM   test t
       ORDER  BY t.ts;            -- in chronological order
   rec record;                    -- for current row from cursor
   rn  int;
BEGIN
   OPEN cur; FETCH cur INTO rec; -- open cursor,  fetch first row
   ct := -1;                     -- init; -1 covers special case at start

   FOR id, ts, rn IN
      SELECT t.id, t.ts, row_number() OVER (ORDER BY t.ts)
      FROM   test t
      ORDER  BY t.ts             -- in same chronological order as cursor
   LOOP
      IF rec.ts1 >= ts THEN      -- still in range ... 
         ct := ct + 1;           -- ... just increment
      ELSE                       -- out of range ...
         LOOP                    -- ... advance cursor
            FETCH cur INTO rec;
            EXIT WHEN rec.ts1 >= ts; -- earliest row within time frame
         END LOOP;
         ct := rn - rec.rn;      -- new count
      END IF;
      RETURN NEXT;
   END LOOP;
END
$func$ LANGUAGE plpgsql;
```
요구:
```
SELECT * FROM running_window_ct();
```
SQL 바이올린.

데비안에 PostgreSQL의 9.1.9) : 내 기존 테스트 서버에서 빠른 벤치 마크를 실행 위의 테이블.
```
-- TRUNCATE test;
INSERT INTO test
SELECT g, '2013-08-08'::timestamp
         + g * interval '5 min'
         + random() * 300 * interval '1 min' -- halfway realistic values
FROM   generate_series(1, 10000) g;

CREATE INDEX test_ts_idx ON test (ts);
ANALYZE test;  -- temp table needs manual analyze
```
나는 각 실행의 굵은 부분을 변경하고 분석 EXPLAIN와 5의 장점을했다.

100 개 행 ROM : 27.656 MS ARR : 7.834 MS COR : 5.488 MS FNC : 1.115 MS

1000 행 ROM : 2116.029 MS ARR : 189.679 MS COR : 65.802 MS FNC : 8.466 MS

5000 행 ROM : 51,347 MS !! ARR : 3167 MS COR : 333 밀리 초 FNC : 42 MS

100000 행 ROM : DNF ARR : DNF COR : 6760 MS FNC : 828 밀리 초

이 함수는 분명 승자입니다. 그것은 크기와 규모 최선을의 순서로 가장 빠른 것입니다. 배열 처리는 경쟁 할 수 없다.
==============================
2.이 배열에 모든 요소를 결합하기 때문에 내 이전의 시도가 잘 수행되지 않고 업데이트하고, 그게 내가하고 싶었던 게 아니에요. 그래서 여기에 업데이트 된 버전입니다 - 자기가 가입하거나 커서와 기능으로도 수행하지,하지만 내 이전과 같은 끔찍한 아니다 :

이 배열에 모든 요소를 결합하기 때문에 내 이전의 시도가 잘 수행되지 않고 업데이트하고, 그게 내가하고 싶었던 게 아니에요. 그래서 여기에 업데이트 된 버전입니다 - 자기가 가입하거나 커서와 기능으로도 수행하지,하지만 내 이전과 같은 끔찍한 아니다 :
```
CREATE OR REPLACE FUNCTION agg_array_range_func
(
  accum anyarray,
  el_cur anyelement,
  el_start anyelement,
  el_end anyelement
)
returns anyarray
as
$func$
declare
    i int;
    N int;
begin
    N := array_length(accum, 1);
    i := 1;
    if N = 0 then
        return array[el_cur];
    end if;
    while i <= N loop
        if accum[i] between el_start and el_end then
            exit;
        end if;
        i := i + 1;
    end loop;
    return accum[i:N] || el_cur;
end;
$func$
LANGUAGE plpgsql;

CREATE AGGREGATE agg_array_range
(
    anyelement,
    anyelement,
    anyelement
)
(
  SFUNC=agg_array_range_func,
  STYPE=anyarray
);

select
    id, ts,
    array_length(
        agg_array_range(ts, ts - interval '1 hour', ts) over (order by ts)
    , 1) - 1
from test;
```
내 로컬 컴퓨터와 sqlfiddle에서 테스트 한, 실제로 자기는, (내가 놀랐습니다, 내 결과 어윈와 동일하지 않습니다) 다음 어윈 기능과 다음이 집계을 최선을 수행 조인. 당신은 sqlfiddle에서 직접 테스트 할 수 있습니다

이전의 나는 여전히 매우 훑어처럼 PostgreSQL을 학습,하지만 난거야. 이 SQL 서버라면, 나는 XML의 사용을 선택하고 XML을 선택할 것입니다. 나는 PostreSQL에서 작업을 수행하는 방법을 알고 있지만, 그 작업을 위해 더 나은 일이없는 - 배열을! 그래서 여기에 윈도 윙 기능 (I 테이블에 중복 된 날짜가 있다면 그것이 제대로 작동 것이라고 생각하고, 자기가 가입보다 더 잘 수행한다면 나도 모르는) 내 CTE를이다 :
```
with cte1 as (
    select
        id, ts,
        array_agg(ts) over(order by ts asc) as dates
    from test
), cte2 as (
    select
       c.id, c.ts,
       array(
        select arr
        from (select unnest(dates) as arr) as x
        where x.arr >= c.ts - '1 hour'::interval
       ) as dates
   from cte1 as c
)
select c.id, c.ts, array_length(c.dates, 1) - 1 as cnt
from cte2 as c
```
참조 SQL 바이올린 데모

희망이 도움이

from https://stackoverflow.com/questions/18173412/window-functions-or-common-table-expressions-count-previous-rows-within-range by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 PostgreSQL의에서 배열의 요소에 대한 인덱스를 만드는 방법? (0)	2020.06.15
[SQL] 그것은 오라클의 매개 변수로 테이블 이름을 통과 할 수 있습니까? (0)	2020.06.15
[SQL] 여러 행에 분할 열 (0)	2020.06.15
[SQL] OPENROWSET와 Excel로 SQL 서버 수출 (0)	2020.06.15
[SQL] 오라클 동적 피벗 (0)	2020.06.15

복붙노트

[SQL] 윈도우 함수 또는 공통 테이블 표현식 : 범위 내에서 이전 행을 계산

윈도우 함수 또는 공통 테이블 표현식 : 범위 내에서 이전 행을 계산

해결법

1.자신의 프레임 정의는 정적이지만 동적 프레임이 필요합니다 - 당신이 일반 쿼리, 열팽창 계수 및 창 기능이 싸게 할 수 있다고 생각하지 않습니다.

'SQL' 카테고리의 다른 글

티스토리툴바