sql 기간별 통계 query, group by

const date = new Date()
if (timeunit === 'all') {
} else if (timeunit === 'day') {
regStart = formatDate(date)
regEnd = formatDate(new Date(date.valueOf() + 1000 * 3600 * 24))
console.log(regStart, regEnd)
} else if (timeunit === 'week') { // 이번주 월요일 ~ 일요일
regStart = getMonday(new Date())
regEnd = getMonday(new Date(date.valueOf() + 1000 * 3600 * 24 * 7))
} else if (timeunit === 'month') { // 이번달 1일 ~ 31일
regStart = formatDate(new Date(date.getFullYear(), date.getMonth(), 1))
regEnd = formatDate(new Date(date.getFullYear(), date.getMonth() + 1, 0))
}

function formatDate (date) {
const d = new Date(date)
let month = '' + (d.getMonth() + 1)
let day = '' + d.getDate()
const year = d.getFullYear()
if (month.length < 2) month = '0' + month
if (day.length < 2) day = '0' + day
return [year, month, day].join('-')
}

function getMonday (d) {
d = new Date(d)
const day = d.getDay()
const diff = d.getDate() - day + (day === 0 ? -6 : 1) // adjust when day is sunday
return new Date(d.setDate(diff))
}

월별
SELECT pv_cnt
, uv_cnt
, uv.conn_time
FROM
(SELECT sum(pv_cnt) AS pv_cnt
, to_char(conn_time, 'YYYY-MM') AS conn_time
FROM portal.bmt_pv_conn
GROUP BY to_char(conn_time, 'YYYY-MM')) pv,
(SELECT sum(uv_cnt) AS uv_cnt
, to_char(conn_time, 'YYYY-MM') AS conn_time
FROM portal.bmt_uv_conn
GROUP BY to_char(conn_time, 'YYYY-MM')) uv
WHERE pv.conn_time = uv.conn_time
AND uv.conn_time BETWEEN $1 AND $2
ORDER BY pv.conn_time DESC

모든 일

SELECT
pv_cnt,
uv_cnt,
uv.conn_time
FROM
(SELECT
sum(pv_cnt) AS pv_cnt,
to_char(conn_time, 'YYYY-MM-DD') AS conn_time
FROM portal.bmt_pv_conn
GROUP BY conn_time) pv,
(SELECT
sum(uv_cnt) AS uv_cnt,
to_char(conn_time, 'YYYY-MM-DD') AS conn_time
FROM portal.bmt_uv_conn
GROUP BY
conn_time) uv
WHERE pv.conn_time = uv.conn_time
AND uv.conn_time BETWEEN $1 AND $2
ORDER BY pv.conn_time DESC

SELECT pv_cnt
, uv_cnt
, uv.conn_time
FROM
(SELECT sum(pv_cnt) AS pv_cnt
, ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7) AS conn_time
FROM portal.bmt_pv_conn
GROUP BY ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7)) pv,
(SELECT sum(uv_cnt) AS uv_cnt
, ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7) AS conn_time
FROM portal.bmt_uv_conn
GROUP BY ceil((conn_time - trunc(trunc(conn_time,'mm'),'iw') + 1) / 7)) uv
WHERE pv.conn_time = uv.conn_time
--AND uv.conn_time BETWEEN $1 AND $2
ORDER BY pv.conn_time DESC

SELECT pv_cnt
, uv_cnt
, uv.conn_time
FROM
(SELECT sum(pv_cnt) AS pv_cnt
, date_trunc('week', conn_time) AS conn_time
FROM portal.bmt_pv_conn
GROUP BY date_trunc('week', conn_time)) pv,
(SELECT sum(uv_cnt) AS uv_cnt
, date_trunc('week', conn_time) AS conn_time
FROM portal.bmt_uv_conn
GROUP BY date_trunc('week', conn_time)) uv
WHERE pv.conn_time = uv.conn_time
-- AND uv.conn_time BETWEEN $1 AND $2
ORDER BY pv.conn_time DESC