[SQL] 예외 의해 포스트 그레스 윈도우 함수 그룹

예외 의해 포스트 그레스 윈도우 함수 그룹

나는 시간의 기간 동안, 누적 결과로 사용자 (이익 / 손실)의 통계를 검색하는 쿼리를 함께 넣어 노력하고있어.

여기에 지금까지 가지고있는 쿼리는 다음과 같습니다

SELECT p.name, e.date, 
    sum(sp.payout) OVER (ORDER BY e.date)
    - sum(s.buyin) OVER (ORDER BY e.date) AS "Profit/Loss" 
FROM result r 
    JOIN game g ON r.game_id = g.game_id 
    JOIN event e ON g.event_id = e.event_id 
    JOIN structure s ON g.structure_id = s.structure_id 
    JOIN structure_payout sp ON g.structure_id = sp.structure_id
                            AND r.position = sp.position 
    JOIN player p ON r.player_id = p.player_id 
WHERE p.player_id = 17 
GROUP BY p.name, e.date, e.event_id, sp.payout, s.buyin
ORDER BY p.name, e.date ASC

쿼리가 실행됩니다. 그러나, 결과는 약간 잘못된 것입니다. 그 이유는 이벤트가 (다른 sp.payouts와) 여러 게임을 할 수 있다는 것입니다. 사용자가 서로 다른 배당금을 갖는 경우에 2 개 개의 결과를 갖는다 (이벤트 당 4 개 개의 게임이있다, 즉, 및 사용자가 하나의 £ 20와 서로 £ 40 얻는) 경우에 따라서, 상기 복수의 행이 나온다.

확실한 해결책은에 의해 그룹을 수정하는 것입니다 :

GROUP BY p.name, e.date, e.event_id

그것은 그 sp.payout을 인식 할 나타나지 않고 s.buyin가 집계 함수 내부에있는 그러나, 포스트 그레스는이에 뿌려줍니다. 나는 오류가 발생합니다 :

우분투 리눅스 서버 9.1를 실행하고 있습니다. 나는 뭔가를 놓치고, 또는이 포스트 그레스의 진정한 결함이 될 수 있을까?

해결법

1. ==============================

   1.당신은 집계 함수를 사용하여, 사실이 아니다. 당신은 윈도우 기능을 사용하고 있습니다. PostgreSQL의이 sp.payout 및 s.buyin이 GROUP BY 절에 포함되도록 요구하는 이유입니다.

   당신은 집계 함수를 사용하여, 사실이 아니다. 당신은 윈도우 기능을 사용하고 있습니다. PostgreSQL의이 sp.payout 및 s.buyin이 GROUP BY 절에 포함되도록 요구하는 이유입니다.

   오버 절을 추가하여, 총 합 함수 ()는 모든 행을 유지하면서 파티션마다 값을 합산 윈도우 함수로 설정된다.

   당신은 윈도우 함수 및 집계 기능을 결합 할 수 있습니다. 집계 먼저인가된다. 난 당신이 이벤트에 여러 지불금 / buyins을 처리하는 방법 설명에서 이해하지 않았다. 추측, 나는 이벤트 당 그들의 합을 계산합니다. 지금은 GROUP BY 절에서 sp.payout 및 s.buyin을 제거하고 플레이어와 이벤트 당 하나 개의 행을 얻을 수 있습니다 :

   SELECT p.name
        , e.event_id
        , e.date
        , sum(sum(sp.payout)) OVER w
        - sum(sum(s.buyin  )) OVER w AS "Profit/Loss" 
   FROM   player            p
   JOIN   result            r ON r.player_id     = p.player_id  
   JOIN   game              g ON g.game_id       = r.game_id 
   JOIN   event             e ON e.event_id      = g.event_id 
   JOIN   structure         s ON s.structure_id  = g.structure_id 
   JOIN   structure_payout sp ON sp.structure_id = g.structure_id
                             AND sp.position     = r.position
   WHERE  p.player_id = 17 
   GROUP  BY e.event_id
   WINDOW w AS (ORDER BY e.date, e.event_id)
   ORDER  BY e.date, e.event_id;
   

   이 식에서 : 합 (SUM (sp.payout)) OVER w 외측 합 ()는 윈도우 함수이고, 내측 합 ()는 집합 함수이다.

   가정 p.player_id 및 e.event_id는 해당 테이블의 PRIMARY KEY 있습니다.

   나는 결정 정렬 순서에 도달하기 위해 윈도우 조항에 의해 순서에 e.event_id을 추가했다. (같은 날짜에 여러 이벤트가있을 수 있습니다.) 또한 하루에 여러 이벤트를 구별하기 결과에 EVENT_ID 포함되어 있습니다.

   동안 (p.player_id = 17), 우리는 GROUP BY와 ORDER BY에 p.name 또는 p.player_id을 추가 할 필요가 없습니다 싱글 플레이어에 대한 쿼리 제한합니다. 중 하나가 과도하게 열을 곱 것 조인하면 결과 합계가 잘못된 것 (부분적으로 또는 완전히 곱). p.name에 의해 그룹화하면 다음 쿼리를 복구 할 수 있습니다.

   또한 GROUP BY 절에서 e.date를 제거했습니다. 기본 키는 PostgreSQL의 9.1 이후 커버를 입력 행의 모든 ​​열을 e.event_id.

   당신이 한 번에 여러 선수를 반환하는 쿼리를 변경하는 경우, 적응 :

   ...
   WHERE  p.player_id < 17  -- example - multiple players
   GROUP  BY p.name, p.player_id, e.date, e.event_id  -- e.date and p.name redundant
   WINDOW w AS (ORDER BY p.name, p.player_id, e.date, e.event_id)
   ORDER  BY p.name, p.player_id, e.date, e.event_id;
   

   p.name이 player_id에 의해 독특한 (?), 그룹 및 순서를 정의하지 않는 한 추가로 결정 정렬 순서에서 올바른 결과를 얻을 수 있습니다.

   난 단지 성능이 향상 주길 기대하면서 모든 조항에 동일한 정렬 순서를 가지고 GROUP BY에 e.date 및 p.name을 유지했다. 그 밖에, 당신이 열을 제거 할 수 있습니다. (첫 번째 질의에서 단지 e.date에 대한 유사.)

from https://stackoverflow.com/questions/8844903/postgres-window-function-and-group-by-exception by cc-by-sa and MIT license