일자별 로그 갯수 - Heeyoung-Ahn/MariaDB-with-Excel-VBA GitHub Wiki

일자별 로그 갯수를 CTE(Common Table Expressions) 이용하여 집계

WITH daily_log AS (
    SELECT CONVERT(a.time_stamp, DATE) AS log_dt, a.user_nm
        FROM co_account.v_logs a
)
SELECT log_dt, user_nm, COUNT(log_dt)
    FROM daily_log
    GROUP BY log_dt, user_nm WITH ROLLUP;

---

참조 ROLLUP 문법

• 참조: https://mariadb.com/kb/en/select-with-rollup/

SELECT country, year, genre, SUM(sales) 
  FROM booksales GROUP BY country, year, genre WITH ROLLUP;

SELECT country, year, genre, SUM(sales) 
  FROM booksales GROUP BY country, year, genre WITH ROLLUP LIMIT 4;

SELECT country, year, genre, SUM(sales) 
  FROM booksales GROUP BY country, year DESC, genre WITH ROLLUP;

---

참조 CTE

-- 특정일(2019-01-31)부터 전월말까지 조회월을 목록으로 구성
WITH RECURSIVE query_month AS (
  SELECT '2019-01-31' AS q_month
  UNION ALL
  SELECT LAST_DAY(q_month + INTERVAL 1 MONTH)
    FROM query_month
    WHERE q_month < LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH)
)
SELECT q_month FROM query_month;