작년도의 액티브 오더를 받는 방법

작년도의 액티브 오더를 받는 방법

시작일과 종료일이 있는 테이블이 있습니다.그 표를 바탕으로 나는 지난 12개월 동안의 액티브 오더를 받아야 한다.

종료일 NULL은 지금까지 활성 오더를 의미합니다.

표 기본 개념

예상되는 출력

과거의 경험에 비추어 볼 때 올해 1월에는 이렇게 쿼리를 작성할 수 있지만 위 그림과 같이 모든 달 동안 필요합니다.

select
      mnth.num, count(*)
from (
    select 1 AS num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all 
    select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12
    ) mnth
left join (    
      select
            o.id
          , case 
               when o.start_date < date_format(current_date(), '%Y-01-01') then 1
               else month(o.start_date)
               end AS start_month
          , case 
               when o.end_date < date_format(current_date(), '%Y-01-01') then 0
               when o.end_date >= date_format(current_date(), '%Y-01-01') then month(o.end_date)
               else month(current_date())
               end AS end_month
      from order o
      ) active_work_orders on mnth.num between active_work_orders.start_month and active_work_orders.end_month  
where mnth.num <= month(current_date())
group by
      mnth.num
;

출력을 바탕으로 그래프를 생성해야 합니다.잘 부탁드립니다.

바이올린 링크 여기를 클릭

아마 이런 느낌일 거야

select mthnum,
        count(*) 
from
(
select mthnum, o.*,year(start_date)* 12 + month(start_date),year(end_date) * 12 + month(end_date)
from
(
    #select 2016*12 + 11 AS mthnum union all select 2016*12 + 12 union all select 2017*12 + 1 union all select 2017*12 + 2
    select 2017*100 + 2 as mthnum
     union all select 2017*100 + 3 union all select 2017*100 + 4 union all select 2017*100 + 5 union all select 2017*100 + 6
     union all select 2017*100 + 7 union all select 2017*100 + 8 union all select 2017*100 + 9 union all select 2017*100 + 10
     union all select 2017*100 + 11 union all select 2017*100 + 12 union all select 2018*100 + 1
) mth
cross join
(select * from order_test_new #where order_num = 1288
) o
) s where (s.mthnum >= year(s.start_date)*100  + month(start_date))  and 
    (end_date is null or s.mthnum <= year(end_date) * 100 + month(s.end_date))
group by mthnum

결과

+--------+----------+
| mthnum | count(*) |
+--------+----------+
| 201702 |        9 |
| 201703 |        9 |
| 201704 |        9 |
| 201705 |        8 |
| 201706 |        8 |
| 201707 |        8 |
| 201708 |        8 |
| 201709 |        7 |
| 201710 |        7 |
| 201711 |        6 |
| 201712 |        6 |
| 201801 |        6 |
+--------+----------+
12 rows in set (0.00 sec)

그러면 end_date 월에 end_date가 있는 주문이 활성화되어 있는 것으로 간주됩니다.

함수의 도움을 받아 연도와 월을 추출하고group by그걸 근거로 해서.

그 후, 표시에는 함수를 사용할 수 있습니다.

자, 이렇게 합시다.

SELECT 
    DATE_FORMAT(start_date, '%b-%y') AS `Month`, COUNT(*) AS `Active`
FROM
    orders
WHERE
    end_date IS NULL
GROUP BY EXTRACT(YEAR_MONTH FROM start_date)

지난 12개월의 결과만 얻으려면 다음을 사용할 수 있습니다.

SELECT 
    `month_list`.`month`, IFNULL(`active_orders`.`active_count`, 0) AS `active_count`
FROM 
    (SELECT 
        *
    FROM
        (SELECT DATE_FORMAT(NOW(), '%b-%y') AS `month` 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 4 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 5 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 6 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 7 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 8 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 9 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 10 MONTH, '%b-%y') 
            UNION 
        SELECT DATE_FORMAT(NOW() - INTERVAL 11 MONTH, '%b-%y')) 
    AS `months`) as `month_list`
LEFT JOIN
    (SELECT 
        DATE_FORMAT(start_date, '%b-%y') AS `month`, COUNT(*) AS `active_count`
    FROM
        `order`
    WHERE
        end_date IS NULL
    GROUP BY EXTRACT(YEAR_MONTH FROM start_date)) as `active_orders` on `month_list`.`month` = `active_orders`.`month`;

언급URL : https://stackoverflow.com/questions/48415357/how-to-get-active-orders-for-past-year