MySQL에서 누적 합계 열 생성

MySQL에서 누적 합계 열 생성

다음과 같은 테이블이 있습니다.

id   count
1    100
2    50
3    10

cumulative_sum이라는 새 열을 추가하고 테이블은 다음과 같습니다.

id   count  cumulative_sum
1    100    100
2    50     150
3    10     160

쉽게 할 수 있는 MySQL update 문이 있나요?어떻게 하면 이 일을 할 수 있을까요?

연관된 쿼리 사용:

---

  SELECT t.id,
         t.count,
         (SELECT SUM(x.count)
            FROM TABLE x
           WHERE x.id <= t.id) AS cumulative_sum
    FROM TABLE t
ORDER BY t.id

MySQL 변수 사용:

---

  SELECT t.id,
         t.count,
         @running_total := @running_total + t.count AS cumulative_sum
    FROM TABLE t
    JOIN (SELECT @running_total := 0) r
ORDER BY t.id

주의:

• 그JOIN (SELECT @running_total := 0) r크로스 조인(cross join)으로 별도의 필요 없이 변수 선언을 할 수 있습니다.SET명령어를 입력합니다.
• 테이블 에일리어스,r서브쿼리/파생 테이블/인라인 뷰에는 MySQL이 필요합니다.

경고:

• MySQL 고유, 다른 데이터베이스로 이식할 수 없음
• 그ORDER BY중요한 것은 순서가 OP와 일치하고 보다 복잡한 변수 사용에 큰 영향을 미칠 수 있기 때문입니다(IE: psuedo ROW_NUMBER/RANK 기능, MySQL에는 없습니다).

성능에 문제가 있는 경우 MySQL 변수를 사용할 수 있습니다.

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

또는 다음 명령어를 삭제할 수도 있습니다.cumulative_sum각 쿼리에 대해 열을 지정하고 계산합니다.

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

이것에 의해, 실행중의 합계가 계산됩니다.

MySQL 8.0/MariaDB는 windowed를 지원합니다.

SELECT *, SUM(cnt) OVER(ORDER BY id) AS cumulative_sum
FROM tab;

출력:

┌─────┬──────┬────────────────┐
│ id  │ cnt  │ cumulative_sum │
├─────┼──────┼────────────────┤
│  1  │ 100  │            100 │
│  2  │  50  │            150 │
│  3  │  10  │            160 │
└─────┴──────┴────────────────┘

db <> 삭제

UPDATE t
SET cumulative_sum = (
 SELECT SUM(x.count)
 FROM t x
 WHERE x.id <= t.id
)

select Id, Count, @total := @total + Count as cumulative_sum
from YourTable, (Select @total := 0) as total ;

샘플 쿼리

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(date) AS d,
       COUNT(*) AS c
    FROM  orders
    WHERE  hasPaid > 0
    GROUP  BY d
    ORDER  BY d) AS q1

또한 각 삽입 전에 합계를 계산하는 트리거를 만들 수 있습니다.

delimiter |

CREATE TRIGGER calCumluativeSum  BEFORE INSERT ON someTable
  FOR EACH ROW BEGIN

  SET cumulative_sum = (
     SELECT SUM(x.count)
        FROM someTable x
        WHERE x.id <= NEW.id
    )

    set  NEW.cumulative_sum = cumulative_sum;
  END;
|

테스트하지 않았습니다.

tableName에서 id, count, sum(count)over(count description에 의한 순서)를 cumulative_sum으로 선택합니다.

카운트 컬럼의 합계 함수를 사용한 후 over 절을 사용했습니다.각 행이 개별적으로 요약됩니다.첫 번째 행은 그냥 100이 될 것이다.두 번째 행은 100+50이 될 것이다.세 번째 행은 100+50+10 등입니다.즉, 기본적으로 각 행은 해당 행과 이전 행의 합계이며 마지막 행은 모든 행의 합계입니다.각 행은 ID가 자신보다 작거나 같은 양의 합입니다.

  select t1.id, t1.count, SUM(t2.count) cumulative_sum
    from table t1 
        join table t2 on t1.id >= t2.id
    group by t1.id, t1.count

단계별:

1 - 다음 표가 제시되어 있습니다.

select *
from table t1 
order by t1.id;

id  | count
 1  |  11
 2  |  12   
 3  |  13

2 - 그룹별 정보 가져오기

select *
from table t1 
    join table t2 on t1.id >= t2.id
order by t1.id, t2.id;

id  | count | id | count
 1  | 11    | 1  |  11

 2  | 12    | 1  |  11
 2  | 12    | 2  |  12

 3  | 13    | 1  |  11
 3  | 13    | 2  |  12
 3  | 13    | 3  |  13

3단계: t1.id 그룹별 합계

select t1.id, t1.count, SUM(t2.count) cumulative_sum
from table t1 
    join table t2 on t1.id >= t2.id
group by t1.id, t1.count;


id  | count | cumulative_sum
 1  |  11   |    11
 2  |  12   |    23
 3  |  13   |    36

언급URL : https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql