실전 — 통계 집계

SELECT board_num, COUNT(*) AS 글수, SUM(CAST(hit_count AS bigint)) AS 조회합
FROM Board.POSTS_BIG
WHERE reg_date >= '2026-06-01' AND reg_date < '2026-07-01'
GROUP BY board_num;

CREATE TABLE Board.STAT_DAILY (
    stat_date   date NOT NULL,
    board_num   int  NOT NULL,

    post_cnt    int  NOT NULL CONSTRAINT DF_STAT_DAILY_post DEFAULT 0,
    comment_cnt int  NOT NULL CONSTRAINT DF_STAT_DAILY_cmt  DEFAULT 0,
    user_cnt    int  NOT NULL CONSTRAINT DF_STAT_DAILY_user DEFAULT 0,
    hit_sum     int  NOT NULL CONSTRAINT DF_STAT_DAILY_hit  DEFAULT 0,

    upd_date    datetime2(0) NOT NULL
        CONSTRAINT DF_STAT_DAILY_upd DEFAULT SYSDATETIME(),

    CONSTRAINT PK_STAT_DAILY PRIMARY KEY CLUSTERED (stat_date, board_num)
);

CREATE OR ALTER PROCEDURE Board.P_STAT_DAILY_BUILD
    @from date,
    @to   date = NULL,          -- 하루만이면 비웁니다
    @rows int  = 0 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    SET @to = ISNULL(@to, @from);
    IF @to < @from THROW 50030, N'끝 날짜가 시작 날짜보다 빠릅니다.', 1;

    DECLARE @outer bit = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;

    BEGIN TRY
        IF @outer = 0 BEGIN TRAN;

        -- 그 기간을 먼저 지웁니다. 이 한 줄이 멱등을 만듭니다(6.3).
        DELETE FROM Board.STAT_DAILY WHERE stat_date BETWEEN @from AND @to;

        ;WITH P AS (                                   -- 글 쪽 (2.5)
            SELECT CAST(reg_date AS date) AS d, board_num,
                   COUNT(*) AS post_cnt,
                   COUNT(DISTINCT user_num) AS user_cnt,
                   SUM(hit_count) AS hit_sum
            FROM Board.POSTS
            WHERE reg_date >= @from
              AND reg_date < DATEADD(day, 1, @to)   -- 범위로(5.4)
            GROUP BY CAST(reg_date AS date), board_num
        ), C AS (                                   -- 댓글 쪽
            SELECT CAST(c.reg_date AS date) AS d, p.board_num,
                   COUNT(*) AS comment_cnt
            FROM Board.COMMENTS c
                JOIN Board.POSTS p ON p.num = c.post_num
            WHERE c.reg_date >= @from
              AND c.reg_date < DATEADD(day, 1, @to)
            GROUP BY CAST(c.reg_date AS date), p.board_num
        )
        INSERT INTO Board.STAT_DAILY
            (stat_date, board_num, post_cnt, comment_cnt, user_cnt, hit_sum)
        SELECT ISNULL(P.d, C.d), ISNULL(P.board_num, C.board_num),
               ISNULL(P.post_cnt, 0), ISNULL(C.comment_cnt, 0),
               ISNULL(P.user_cnt, 0), ISNULL(P.hit_sum, 0)
        FROM P FULL OUTER JOIN C                     -- 한쪽만 있는 날도 담습니다(2.1)
             ON C.d = P.d AND C.board_num = P.board_num;

        SET @rows = @@ROWCOUNT;

        IF @outer = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @outer = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH
END

CREATE TABLE Board.STAT_MONTHLY (
    stat_month  char(7) NOT NULL,
    board_num   int NOT NULL,
    post_cnt    int NOT NULL,
    comment_cnt int NOT NULL,
    user_cnt    int NOT NULL,
    hit_sum     int NOT NULL,
    CONSTRAINT PK_STAT_MONTHLY PRIMARY KEY CLUSTERED (stat_month, board_num)
);
GO

INSERT INTO Board.STAT_MONTHLY
    (stat_month, board_num, post_cnt, comment_cnt, user_cnt, hit_sum)
SELECT CONVERT(char(7), stat_date, 23), board_num,
       SUM(post_cnt), SUM(comment_cnt), MAX(user_cnt), SUM(hit_sum)
FROM Board.STAT_DAILY
GROUP BY CONVERT(char(7), stat_date, 23), board_num;

-- 어제치를 셉니다. 오늘 것은 아직 쌓이는 중입니다(6.6).
DECLARE @yesterday date = CAST(DATEADD(day, -1, SYSDATETIME()) AS date);
DECLARE @rows int;

EXEC Board.P_STAT_DAILY_BUILD @from = @yesterday, @rows = @rows OUTPUT;

-- 며칠 전 것까지 함께 다시 세면 늦게 들어온 자료를 잡습니다.
EXEC Board.P_STAT_DAILY_BUILD
     @from = DATEADD(day, -3, @yesterday), @to = @yesterday, @rows = @rows OUTPUT;

-- 최근 7일 가운데 집계가 빠진 날이 있는지 봅니다.
WITH D AS (
    SELECT CAST(DATEADD(day, -n, SYSDATETIME()) AS date) AS d
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) v(n)
)
SELECT D.d AS 빠진날
FROM D
WHERE NOT EXISTS (SELECT 1 FROM Board.STAT_DAILY s WHERE s.stat_date = D.d)
ORDER BY D.d;

-- 언제 채웠는지도 봅니다.
SELECT MAX(stat_date) AS 마지막집계일,
       MAX(upd_date)  AS 마지막실행시각,
       DATEDIFF(hour, MAX(upd_date), SYSDATETIME()) AS 몇시간전
FROM Board.STAT_DAILY;