질의 결과를 기반으로 하위 질의 가능 UNION ALL 만들기
데이터.
저는 다음과 같은 테이블을 몇 개 있습니다.
CREATE TABLE cycles (
`cycle` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cycle_type` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`start` date DEFAULT NULL,
`end` date DEFAULT NULL
);
CREATE TABLE rsvn (
`str` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL
);
INSERT INTO `cycles` (`cycle`, `cycle_type`, `start`, `end`) values
('202013', 'a', '2021-01-04', '2021-01-31'),
('202013', 'b', '2021-01-04', '2021-01-31'),
('202101', 'a', '2021-01-04', '2021-01-31'),
('202101', 'b', '2021-01-04', '2021-01-31'),
('202102', 'a', '2021-02-01', '2021-02-28'),
('202102', 'b', '2021-02-01', '2021-02-28'),
('202103', 'a', '2021-03-01', '2021-03-28'),
('202103', 'b', '2021-03-01', '2021-03-28');
INSERT INTO `rsvn` (str, start_date, end_date) values
('STR01367', '2020-12-07', '2020-06-21'),
('STR00759', '2020-12-07', '2021-04-25'),
('STR01367', '2021-01-04', '2021-09-12'),
('STR01367', '2021-06-21', '2022-02-27');
원하는 결과
임의의 주어진 주기에 대해 교차 주기의 발생 횟수를 세고자 합니다.그래서 2108-2108 주기(한 주기) 사이에 다음과 같은 것이 보입니다.
str | 세어보세요 |
---|---|
STR01367 | 1 |
STR00759 | 1 |
그리고 2108년에서 2109년 사이(2주기)에 저는 다음과 같이 봅니다.
str | 세어보세요 |
---|---|
STR01367 | 2 |
STR00759 | 1 |
내가 시도한 것
그 결과를 동적으로 얻는 방법을 찾고 있습니다.UNION ALL 쿼리(주기마다 하나씩 쿼리) 이외의 옵션이 보이지 않아 PROCEDURE를 작성해 보았습니다.하지만 질의 결과에 대한 사후 처리를 하고 싶어서 안되었고, CTE나 서브쿼리에서 절차 결과를 사용할 수는 없다고 생각합니다.
내 프로시저(작동, 결과를 하위 쿼리에 포함할 수 없음)SELECT * FROM call count_cycles
(?)):
CREATE PROCEDURE `count_cycles`(start_cycle CHAR(6), end_cycle CHAR(6))
BEGIN
SET @cycles := (
SELECT CONCAT('WITH installed_cycles_count AS (',
GROUP_CONCAT(
CONCAT('
SELECT rsvn.str, 1 AS installed_cycles
FROM rsvn
WHERE "', `cy`.`start`, '" BETWEEN rsvn.start_date AND COALESCE(rsvn.end_date, "9999-01-01")
OR "', `cy`.`end`, '" BETWEEN rsvn.start_date AND COALESCE(rsvn.end_date, "9999-01-01")
GROUP BY rsvn.str
'
)
SEPARATOR ' UNION ALL '
),
')
SELECT
store.chain AS "Chain"
,store.division AS "Division"
,dividers_store AS "Store"
,SUM(installed_cycles) AS "Installed Cycles"
FROM installed_cycles_count r
LEFT JOIN store ON store.name = r.dividers_store
GROUP BY dividers_store
ORDER BY chain, division, dividers_store, installed_cycles'
)
FROM cycles `cy`
WHERE `cy`.`cycle_type` = 'Ad Cycle'
AND `cy`.`cycle` >= CONCAT('20', RIGHT(start_cycle, 4))
AND `cy`.`cycle` <= CONCAT('20', RIGHT(end_cycle, 4))
GROUP BY `cy`.`cycle_type`
);
EXECUTE IMMEDIATE @cycles;
END
또는 재귀적 쿼리를 사용하여 주기를 늘려 결과를 얻으려 했습니다.이를 통해 제가 원하는 주기를 얻을 수 있었습니다.
WITH RECURSIVE xyz AS (
SELECT cy.`cycle`, cy.`start`, cy.`end`
FROM cycles cy
WHERE cycle_type = 'Ad Cycle'
AND `cycle` = '202101'
UNION ALL
SELECT cy.`cycle`, cy.`start`, cy.`end`
FROM xyz
JOIN cycles cy
ON cy.`cycle` = increment_cycle(xyz.`cycle`, 1)
AND cy.`cycle_type` = 'Ad Cycle'
WHERE cy.`cycle` <= '202110'
)
SELECT * FROM xyz;
그런데 예약표에 무한루프를 추가하면 작동이 안 돼요?
WITH RECURSIVE xyz AS (
SELECT cy.`cycle`, 'dr.dividers_store', 1 AS installed_cycles
FROM cycles cy
LEFT JOIN rsvn dr
ON cy.`start` BETWEEN dr.start_date AND COALESCE(dr.end_date, "9999-01-01")
OR cy.`end` BETWEEN dr.start_date AND COALESCE(dr.end_date, "9999-01-01")
WHERE cy.`cycle_type` = 'Ad Cycle'
AND cy.`cycle` = '202101'
UNION ALL
SELECT cy.`cycle`, 'dr.dividers_store', 1 AS installed_cycles
FROM xyz
JOIN cycles cy
ON cy.`cycle` = increment_cycle(xyz.`cycle`, 1)
AND cy.`cycle_type` = 'Ad Cycle'
LEFT JOIN rsvn dr
ON cy.`start` BETWEEN dr.start_date AND COALESCE(dr.end_date, "9999-01-01")
OR cy.`end` BETWEEN dr.start_date AND COALESCE(dr.end_date, "9999-01-01")
WHERE cy.`cycle` <= '202102'
)
SELECT * FROM xyz
CTE 또는 하위 쿼리에 사용할 수 있도록 필요한 결과를 얻으려면 어떤 옵션이 있습니까?
제가 찾고 있는 결과는 2단계 그룹핑을 통해 쉽게 얻을 수 있습니다.이와 같은 것:
WITH sbc AS (
SELECT cy.`cycle`, dr.str, 1 AS 'count'
FROM cycles cy
LEFT JOIN rsvn dr
ON cy.`start` BETWEEN dr.start_date AND dr.end_date
OR cy.`end` BETWEEN dr.start_date AND dr.end_date
WHERE cy.`cycle_type` = 'Ad Cycle'
AND cy.`cycle` BETWEEN '202201' AND '202205'
GROUP BY cy.`cycle`, dr.str
ORDER BY dr.str, cy.`cycle`
)
SELECT `cycle`, str, SUM(`count`) as `count`
FROM sbc
GROUP BY str
CTE는 주기당 rsvn당 하나의 결과를 생성합니다.나중에 필요한 것은 상점별로 그룹화하고 발생 횟수를 세는 것입니다.
더 단순한 것 외에도, 서버가 여러 그룹화 쿼리에 대해 연합을 수행할 필요가 없기 때문에, 이 쿼리는 제가 질문했을 때 고정되어 있던 연합 개념보다 더 빠른 것으로 의심됩니다.그러나 MariaDB가 어떻게 그러한 쿼리를 최적화하는지 이해할 수 없으며 궁금하지만 벤치마크를 실행하여 확인할 시간이 없습니다.
언급URL : https://stackoverflow.com/questions/72236338/crafting-a-subquery-able-union-all-based-on-the-results-of-a-query
'source' 카테고리의 다른 글
Uncatched TypeError: o.widget이 mouse.min.js Wordpress에서 함수가 아닙니다. (0) | 2023.09.11 |
---|---|
Android에서 의도란? (0) | 2023.09.11 |
C에서 strtok과 strsep의 차이점은 무엇입니까? (0) | 2023.09.11 |
Python에서 C를 호출하는 기능 (0) | 2023.09.11 |
봄 부팅 애플리케이션에서 AWS SDK를 초기화하는 방법은? (0) | 2023.09.11 |