질의 결과를 기반으로 하위 질의 가능 UNION ALL 만들기

질의 결과를 기반으로 하위 질의 가능 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