supabase postgresql 데이터 없는 날 포함하여 월간 일별 통계 만들기

supabase  postgresql 데이터 없는 날 포함하여 월간 일별 통계 만들기

postgresql의 데이터 테이블에서 날짜 데이터를 사용하여 일별 통계 만들기

관리자는 선택한 년도와 월에 해당하는 각각의 날짜에 대해서 가입자 현황 그래프를 볼 수 있어야 한다

하지만 매번 api를 호출하는 것은 너무 비효율적이다. 또한 범위를 통해 해당 월에 해당하는 데이터를 전부 가져오고, 클라이언트에서 직접 그룹화하는 것은 가져와야 하는 데이터가 너무 커지고, 처리해야 하는 직접 클라이언트에서 처리해야 하는 부담이 생긴다.

 

유저 정보를 가지는 테이블이고 있고 아래와 같이 날짜 데이터를 사용하여 유저가 가입한 날짜를 가리키는 레코드가 있을 때, 날짜별로 가입한 사용자의 수를 일자 별로 가져올 수 있다면 편할 것이다.

 

postgresql에서는 함수를 이용하여 입력 값에 따라 데이터를 처리하고 테이블을 반환할 수 있다.

date_trunc 함수를 사용하면 특정 년도의 특정 월에 대한 날짜를 가져올 수 있다.

• 주어진 년도의 월 데이터에서 1달을 더하고 1일을 뺀다 -> 해당 월의 마지막 날짜로 이동한다
• 여기서 일 데이터만 가져와서 days_in_month에 저장한다. 여기서 일 = 해당 월의 일 수이다

구한 일 수에 대해서 1부터 반복문을 통해 각각 날짜에 대해서 일치하는 레코드를 구하고 모두 합쳐 테이블로 반환한다.

CREATE OR REPLACE FUNCTION getSignUpCountByMonth(year INT, month INT)
RETURNS TABLE (date DATE, count INT)
AS $$
DECLARE
    days_in_month INT;
BEGIN
    -- 해당 월의 일 수 계산
    SELECT EXTRACT(DAY FROM (DATE_TRUNC('MONTH', MAKE_DATE(year, month, 1)) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')) INTO days_in_month;

    -- 각 날짜에 대한 레코드 수 조회 및 반환
    FOR i IN 1..days_in_month LOOP
        date := MAKE_DATE(year, month, i);
        SELECT COUNT(*) INTO count FROM auth.users WHERE DATE_TRUNC('DAY', created_at) = date;
        RETURN NEXT;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;