[SQL] MySQL은 행의 집합에 누적 합계

MySQL은 행의 집합에 누적 합계

나는 (포함하는 다수의 조인, 노동 조합) 복잡한 쿼리가 그 반환 ID, 일, 시간, 금액이 포함 된 행 세트. 쿼리의 출력은 다음과 같습니다 :

id day    hr  amount 
1   1      1   10       
1   1      2   25       
1   1      3   30        
1   2      1   10       
1   2      2   40       
1   2      2   30        
2   1      1   10       
2   1      2   15        
2   1      3   30       
2   2      1   10       
2   2      2   20      
2   2      2   30  

나는 하루의 모든 시간, 각 ID에 대한 누적 합계를 찾을 필요가있다. 출력은 다음과 같이해야합니다 :

id day    hr  amount cumulative total
1   1      1   10       10
1   1      2   25       35
1   1      3   30       65 
1   2      1   10       10
1   2      2   40       50
1   2      2   30       80 
2   1      1   10       10
2   1      2   15       25 
2   1      3   30       55
2   2      1   10       10
2   2      2   20       30
2   2      2   30       60

첫 번째 출력을 생성 내 초기 쿼리는 다음과 같다 :

select id, day, hr, amount from
( //multiple joins on multiple tables)a
left join
(//unions on multiple tables)b
on a.id=b.id;

두 번째 출력에 설명 된 것처럼 누적 합계를 얻기 위해 SQL 쿼리은 무엇입니까? SET는 솔루션에 사용되어서는 안된다.

감사.

해결법

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

   1.최신 정보

   최신 정보

   MySQL은 8.0을 소개합니다 "윈도우 함수", SQL Server에 대한 동등한 기능 (분할 및 거래-SQL OVER 구문에서 제공하는 순서로) "윈도우 함수"오라클 "분석 기능".

   MySQL을 참조 설명서 12.21 창 기능 https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

   여기에 제공된 대답은 8.0 이전의 MySQL 버전에 대한 접근 방법이다.

   ORIGINAL 답변

   MySQL은 다른 DBMS에서 사용할 수있는 분석 기능과 같은 실행 "누적 합계", 얻을 때 사용하는 유형 분석 기능을 제공하지 않습니다 (Oracle 또는 SQL Server와 같은합니다.)

   그러나, MySQL을 사용, 일부 분석 기능을 에뮬레이션 할 수 있습니다.

   (적어도) 두 가지 가능한 방법이 있습니다 :

   하나는 합계를 얻기 위해 상관 하위 쿼리를 사용하는 것입니다. 이 방법은 큰 세트에 대한 비용, 그리고 외부 쿼리의 조건이 복잡하면 복잡 할 수 있습니다. 정말 "복수는 여러 테이블에 조인"고 복잡한 방법에 따라 달라집니다 것입니다. (불행히도, MySQL은 또한 하나 열팽창 계수를 지원하지 않습니다.)

   다른 방법은 몇 가지 제어 구분 처리를 수행하는, MySQL의 사용자 변수를 사용하는 것입니다. 여기에 "트릭은"(주문 BY를 사용) 한 후 다른 쿼리에서 쿼리를 포장 쿼리의 결과를 소트하는 것입니다.

   나는 후자의 방법의 예를 줄 것이다.

   때문에 MySQL을 수행하는 작업은 cumulative_total 열 필요는 현재 행으로부터 ID와 하루의 값을 이전에 계산되는 순서로 사용자 변수에 저장된다. 먼저이 열을 넣어 단지 가장 쉬운 방법.

   (아래 쿼리) 나 별칭으로 인라인 도면이 이미 설정된 세션에 대비해서 사용자 변수를 초기화 단지있다. 사람들은 이미 값이 할당 된 경우, 우리는 현재 값을 무시하려면, 그 작업을 수행하는 가장 쉬운 방법은 그것들을 초기화하는 것입니다.

   원래 쿼리는 괄호에 싸여 도착, 아래의 예에서는 별칭, C 주어집니다. 우리는 확실히 우리가 순서대로 쿼리에서 행을 처리하는 것이 될 수 있도록 원래의 질의에 대한 유일한 변화는 ORDER BY 절을 추가합니다.

   외부 선택 확인 여부를 현재 행 "일치"이전 행의 ID와 일 값. 만일 그렇게된다면, 우리는 누적 합계로 현재 행에서 금액을 추가합니다. 일치하지 않으면, 우리는 제로로 누적 합계를 재설정하고 현재 행 (또는, 더 간단하게, 단지 현재 행에서 양을 할당)에서 양을 추가 할 수 있습니다.

   우리는 누적 합계의 계산을 수행 한 후 그들이 사용할 수 있도록 우리가 다음 행을 처리 할 때, 우리는 사용자 변수로 현재 행의 ID와 일 값을 저장합니다.

   예를 들면 :

   SELECT IF(@prev_id = c.id AND @prev_day = c.day
            ,@cumtotal := @cumtotal + c.amount
            ,@cumtotal := c.amount) AS cumulative_total
        , @prev_id  := c.id  AS `id`
        , @prev_day := c.day AS `day`
        , c.hr
        , c.amount AS `amount'
     FROM ( SELECT @prev_id  := NULL
                 , @prev_day := NULL
                 , @subtotal := 0
          ) i
     JOIN (
   
            select id, day, hr, amount from
            ( //multiple joins on multiple tables)a
            left join
            (//unions on multiple tables)b
            on a.id=b.id
   
            ORDER BY 1,2,3
          ) c
   

   이 마지막 열로 누적 총, 다른 순서로 열을 반환 할 필요가 있다면, 다음 하나의 옵션은 괄호 세트에 그 전체 문을 포장하고 인라인보기로 해당 쿼리를 사용하는 것입니다 :

   SELECT d.id
        , d.day
        , d.hr
        , d.amount
        , d.cumulative_total
   FROM (
          // query from above
        ) d
   

2. ==============================

   2.당신은 MySQL의 팔에 이상이 있다면, 당신은이에 대한 윈도우 함수를 사용해야합니다. 귀하의 질의는 읽을 것입니다 :

   당신은 MySQL의 팔에 이상이 있다면, 당신은이에 대한 윈도우 함수를 사용해야합니다. 귀하의 질의는 읽을 것입니다 :

   SELECT
     id, day, hr, amount,
     SUM (amount) OVER (PARTITION BY id, day ORDER BY hr) AS `cumulative total`
   FROM t
   

   t가 어디 왼쪽 테이블 B는에 합류했다. 일부 노트 :

3. ==============================

   3.당신이 가서 여기, 여기 당신의 culminative 총은 ...

   당신이 가서 여기, 여기 당신의 culminative 총은 ...

   select f1.id, f1.day, f1.hr, f1.amount, sum(f2.amount) as culminative_total from foo f1
    inner join foo f2 on (f1.day = f2.day and f1.id=f2.id)
    where f2.hr <= f1.hr
    group by f1.id, f1.day, f1.hour;
   

from https://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql by cc-by-sa and MIT license