221019_광고 관리 플랫폼 대행사 센터 제작 35_통계 관련 쿼리 테스트

해당 게시글은 개인 프로젝트인 "광고 관리 플랫폼 대행사 센터 제작" 중
#105 "통계 관련 쿼리 테스트" 이슈를 다루고 있습니다.

1. 진행 사항

(1) 요약

• 통계 쿼리 작성 및 테스트 - 소재
• 통계 쿼리 작성 및 테스트 - 캠페인
• 통계 쿼리 작성 및 테스트 - 광고주
• 통계 기능 작동 방식 구상, 설계

(2) 세부

• 참고 레퍼런스
  https://kimsyoung.tistory.com/m/entry/3%EA%B0%9C-%EC%9D%B4%EC%83%81%EC%9D%98-%ED%85%8C%EC%9D%B4%EB%B8%94-LEFT-JOIN-%ED%95%98%EA%B8%B0
  https://ggmouse.tistory.com/408
• SQL Fiddle로 테스트를 진행 (http://sqlfiddle.com/)
• 테스트용 Schema SQL

CREATE TABLE IF NOT EXISTS category (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;

INSERT INTO category (name) VALUES 
('테스트');

CREATE TABLE IF NOT EXISTS client (
  id VARCHAR(50) NOT NULL,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;

INSERT INTO client (id, name) VALUES
('테스트광고주1', '김쾅쾅'),
('테스트광고주2', '김큥큥');

CREATE TABLE IF NOT EXISTS campaign (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  client_id VARCHAR(50) NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(client_id) REFERENCES client(id)
) DEFAULT CHARSET=utf8;

INSERT INTO campaign (name, client_id) VALUES
('테스트캠페인1', '테스트광고주1'),
('테스트캠페인2', '테스트광고주1'),
('테스트캠페인3', '테스트광고주2');

CREATE TABLE IF NOT EXISTS creative (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  budget BIGINT NOT NULL,
  campaign_id BIGINT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(campaign_id) REFERENCES campaign(id)
) DEFAULT CHARSET=utf8;

INSERT INTO creative (name, budget, campaign_id) VALUES
('테스트소재1', 10000, 1),
('테스트소재2', 5000, 1),
('테스트소재3', 1000, 1),
('테스트소재4', 1000, 2),
('테스트소재5', 500, 2),
('테스트소재6', 5000, 3);

CREATE TABLE IF NOT EXISTS performance (
  id BIGINT AUTO_INCREMENT,
  view BIGINT,
  click BIGINT,
  conversion BIGINT,
  spend BIGINT,
  purchase BIGINT,
  created_at DATETIME,
  creative_id BIGINT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(creative_id) REFERENCES creative(id)
);

INSERT INTO performance (view, click, conversion, spend, purchase, created_at, creative_id) VALUES
(1,1,1,1,1, '2022-10-19', 1),
(2,2,2,2,2, '2022-10-18', 1),
(3,3,3,3,3, '2022-10-17', 1),
(4,4,4,4,4, '2022-10-16', 1),
(5,5,5,5,5, '2022-10-15', 1),
(6,6,6,6,6, '2022-10-14', 1),
(7,7,7,7,7, '2022-10-13', 1),
(3,3,3,3,3, '2022-10-17', 2),
(4,4,4,4,4, '2022-10-16', 2),
(0,0,0,0,0, '2022-10-16', 3),
(2,4,6,8,10, '2022-10-18', 4),
(1,1,1,1,1, '2022-10-17', 4),
(0,5,5,3,2, '2022-10-16', 5),
(1,3,4,5,6, '2022-10-16', 6);

• 소재 내 실적 기간 합계 SQL

SELECT
c.id AS creative_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase),
p.creative_id
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'

• 캠페인의 소재 당 실적 통계 SQL

SELECT
ca.id AS campaign_id,
c.id AS creative_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'
GROUP BY c.id

• 캠페인의 전체 실적 통계 SQL

SELECT
ca.id AS campaign_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'

• 캠페인의 모든 실적 조회 SQL

SELECT
ca.id AS campaign_id,
c.id AS creative_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'
GROUP BY ca.id, c.id

• 캠페인 당 실적 통계 SQL

SELECT
ca.id AS campaign_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'
GROUP BY ca.id

• 모든 광고주의 캠페인 당 실적 통계 SQL

SELECT
cl.id AS client_id,
ca.id AS campaign_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
LEFT JOIN client AS cl ON cl.id = ca.client_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'
GROUP BY cl.id, ca.id

• 특정 광고주의 전체 실적 통계 SQL

SELECT
cl.id AS client_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
LEFT JOIN client AS cl ON cl.id = ca.client_id
WHERE 
p.created_at BETWEEN '2022-10-15' AND '2022-10-19' AND cl.id = '테스트광고주1'

• 모든 광고주의 전체 실적 통계 SQL

SELECT
cl.id AS client_id,
sum(p.view),
sum(p.click),
sum(p.conversion),
sum(p.spend),
sum(p.purchase)
FROM 
performance as p
LEFT JOIN creative AS c ON c.id = p.creative_id
LEFT JOIN campaign AS ca ON ca.id = c.campaign_id
LEFT JOIN client AS cl ON cl.id = ca.client_id
WHERE p.created_at BETWEEN '2022-10-15' AND '2022-10-19'
GROUP BY cl.id

2. 진행 예정 사항 및 고려할 사항

• SQL 테스트는 성공적으로 완료하였다
• 다만 기능 구현 시 확인해야 할 사항이 있다
  • Querydsl 사용 시 Projection을 할 Dto 클래스를 다 따로 만들어야 하는가?
  • List 를 Page 로 변환하여 페이징 / 정렬을 할 수 있는가?
  • 조회 시 null로 처리되는 값을 Dto 단에서 따로 처리할지, Response 단에서 처리할지? -> Dto에서 null값을 처리하는 부분 (실적 지표 계산 시) 와 Response에서 null값을 처리하는 부분을 분리하여 둘 다 적용
• 실적 표시용 Dto가 이미 존재하는데 광고주-캠페인-소재 통계 시마다 추가되는 것은 각각의 id, 이름이므로 이 필드들을 추가하고 각 통계마다 다르게 주입하는 방법은 어떨지 (ex : 광고주 단위에서는 광고주, 캠페인, 소재 모두 표시, 캠페인 단위에서는 캠페인, 소재 표시 등...)
• 일단 다음과 같이 진행하고자 한다
  • 통계 값에 사용할 Dto는 기존에 만들어둔 PerformanceStatisticsDto 를 수정하여 사용하되, 통계 메소드마다 필드값을 다르게 주입하여 사용한다.
  • List -> Page 변환 방식은 알고 있는데 실제로 페이징 / 정렬이 가능한지 적용해보고 나서 판단해본다...
  • 통계 값에서 확인된 null 값 중 실적 지표 (CTR,CVR,CPA,ROAS 등) 은 Dto에서 처리를 하고 그 외 횟수, 클릭수 등에 대한 값은 Response에서 처리를 하도록 한다.