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