합계가 값보다 작은 행을 테이블에서 가져옵니다.

합계가 값보다 작은 행을 테이블에서 가져옵니다.

테이블에 다음 행이 있다고 가정합니다.

|  id |   user_id   |  amount |      date      |
------------------------------------------------
|  1  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  2  |     2       |  100    |   2019-09-30   |
------------------------------------------------
|  3  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  4  |     3       |  100    |   2019-10-01   |
------------------------------------------------
|  5  |     1       |  75     |   2019-10-01   |
------------------------------------------------
|  6  |     3       |  100    |   2019-10-01   |
------------------------------------------------
|  7  |     1       |  35     |   2019-10-01   |
------------------------------------------------

나는 모든 일을 할 수 있는 방법을 찾고 있다.user_id = 1어디서sum(amount) < 300그리고.date <= '2019-10-01'.

제가 하려는 것은 특정 임계값 합계를 충족하는 레코드만 처리하는 것입니다.어디서부터 시작해야 할지 잘 모르겠어요.

예상 결과

|  id |   user_id   |  amount |      date      |
------------------------------------------------
|  1  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  3  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  5  |     1       |   75    |   2019-10-01   |
------------------------------------------------

내가 지금까지 해 본 것은 이렇다

SELECT id, SUM(amount) as total_sum 
   FROM table 
   WHERE date <= '2019-10-01' AND user_id = 1
     ORDER BY date ASC
   HAVING total_sum <= 300

위의 쿼리에서 원하는 출력을 얻을 수 없습니다.

현재 사용 중인 MySQL 버전: 5.7.25

MySQL이 같은 작업을 수행하려고 할 때 합계가 임계값보다 큰 레코드를 선택한다는 질문을 살펴보았습니다만, 제가 보고 있는 것은 아닙니다.

롤링섬 문제입니다.MySQL 8.0.2 이상에서는 프레임이 있는 Window 기능을 사용하여 해결할 수 있습니다.이전 버전에서는 사용자 정의 세션 변수를 사용하여 동일한 작업을 수행할 수 있습니다.

• 먼저 세션 변수를 사용하여 롤링섬을 계산합니다.
• 그런 다음 파생 테이블의 결과 집합을 사용하여 총합이 "배리어" 300을 교차하는 ID를 찾습니다.새 롤링 합계가 300보다 클 때 장벽에 도달합니다.이 시점에서 장벽 값은 1로 설정하고, 그 앞의 행은 0으로 설정하고, 그 이후의 행은 2 이상으로 설정합니다.
• barrier가 0인 행만 고려하겠습니다.

시도(모든 MySQL 버전에서 작동):

쿼리 #1

SELECT dt.id, 
       dt.user_id, 
       dt.amount, 
       dt.date
FROM 
(
  SELECT 
    t.id, 
    t.user_id, 
    t.amount, 
    t.date, 
    @barrier := CASE
                  WHEN 
                       (@tot_qty := @tot_qty + t.amount) > 300
                  THEN (@barrier + 1)
                  ELSE 0
                END AS barrier 
  FROM 
    your_table AS t 
  CROSS JOIN (SELECT @tot_qty := 0, 
                     @barrier := 0) AS user_init
    WHERE t.user_id = 1 
        AND t.date <= '2019-10-01' 
  ORDER BY t.user_id, t.date, t.id
) AS dt 
WHERE dt.barrier = 0 
ORDER BY dt.user_id, dt.date, dt.id;

결과

| id  | user_id | amount | date       |
| --- | ------- | ------ | ---------- |
| 1   | 1       | 100    | 2019-09-30 |
| 3   | 1       | 100    | 2019-09-30 |
| 5   | 1       | 75     | 2019-10-01 |

---

DB Fielen 보기

세션 변수를 사용하는 것을 좋아하지 않는 경우(일부 경험 많은 SO 사용자는 이를 매우 싫어함), "Self-Join(셀프 조인)"에 기반한 기술을 사용하여 다음을 사용할 수 있습니다.GROUP BY와 함께HAVING걸러낼 수 있습니다.

일반적인 생각은 join을 탈퇴하고 특정 user_id의 이전 행을 가져온 다음 집약하여 롤링섬을 얻은 다음 Having 절을 사용하여 필터링하는 것입니다.

쿼리

SELECT 
  t1.* 
FROM 
  your_table AS t1 
LEFT JOIN your_table AS t2 
  ON t2.user_id = t1.user_id 
     AND t2.date <= t1.date 
     AND t2.id <= t1.id 
WHERE t1.user_id = 1 
  AND t1.date <= '2019-10-31'
GROUP BY t1.user_id, t1.date, t1.id, t1.amount
HAVING COALESCE(SUM(t2.amount),0) < 300;

결과

| id  | user_id | amount | date       |
| --- | ------- | ------ | ---------- |
| 1   | 1       | 100    | 2019-09-30 |
| 3   | 1       | 100    | 2019-09-30 |
| 5   | 1       | 75     | 2019-10-01 |

DB Fielen 보기

두 가지 방법을 모두 벤치마킹하여 적합한 방법을 결정할 수 있습니다.

이 쿼리의 경우 복합 인덱스가 필요합니다.(user_id, date)

언급URL : https://stackoverflow.com/questions/58200598/get-rows-from-table-whose-sum-is-less-than-value