[postgresql]기간 (월, 주, 일) 추출

December 29, 2017

all

SELECT
                  pvcnt,
                  uvcnt,
                  uv.conntime
                FROM
                  (SELECT
                     sum(pvcnt)                      AS pvcnt,
                     tochar(conntime, 'YYYY-MM-DD') AS conntime
                   FROM portal.bmtpvconn
                   GROUP BY conntime) pv,
                  (SELECT
                     sum(uvcnt)                      AS uvcnt,
                     tochar(conntime, 'YYYY-MM-DD') AS conntime
                   FROM portal.bmtuvconn
                   GROUP BY
                     conntime) uv
                WHERE pv.conntime = uv.conntime
                      AND uv.conntime BETWEEN $1 AND $2
                ORDER BY pv.conntime DESC

이번 주 (월~ 일) :

SELECT pvcnt , uvcnt , uv.conntime FROM (SELECT sum(pvcnt) AS pvcnt , ceil((conntime - trunc(trunc(conntime,'mm'),'iw') + 1) / 7) AS conntime FROM portal.bmtpvconn GROUP BY ceil((conntime - trunc(trunc(conntime,'mm'),'iw') + 1) / 7)) pv, (SELECT sum(uvcnt) AS uvcnt , ceil((conntime - trunc(trunc(conntime,'mm'),'iw') + 1) / 7) AS conntime FROM portal.bmtuvconn GROUP BY ceil((conntime - trunc(trunc(conntime,'mm'),'iw') + 1) / 7)) uv WHERE pv.conntime = uv.conntime AND uv.conntime BETWEEN $1 AND $2 ORDER BY pv.conntime DE

SELECT pvcnt , uvcnt , uv.conntime FROM (SELECT sum(pvcnt) AS pvcnt , datetrunc('week', conntime) AS conntime FROM portal.bmtpvconn GROUP BY datetrunc('week', conntime)) pv, (SELECT sum(uvcnt) AS uvcnt , datetrunc('week', conntime) AS conntime FROM portal.bmtuvconn GROUP BY datetrunc('week', conntime)) uv WHERE pv.conntime = uv.conntime AND uv.conntime BETWEEN $1 AND $2 ORDER BY pv.conntime DESC;

이번달 (1일 ~31일) :

                 SELECT pvcnt
  , uvcnt
  , uv.conntime
FROM
  (SELECT sum(pvcnt)                      AS pvcnt
     , tochar(conntime, 'YYYY-MM') AS conntime
   FROM   portal.bmtpvconn
   GROUP BY tochar(conntime, 'YYYY-MM')) pv,
  (SELECT sum(uvcnt)                      AS uvcnt
     , tochar(conntime, 'YYYY-MM') AS conntime
   FROM   portal.bmtuvconn
   GROUP BY tochar(conntime, 'YYYY-MM')) uv
WHERE pv.conntime = uv.conntime
      AND uv.conntime BETWEEN $1 AND $2
ORDER BY pv.conntime DESC

오늘 :

SELECT
                  pvcnt,
                  uvcnt,
                  uv.conntime
                FROM
                  (SELECT
                     sum(pvcnt)                      AS pvcnt,
                     tochar(conntime, 'YYYY-MM-DD') AS conntime
                   FROM portal.bmtpvconn
                   GROUP BY conntime) pv,
                  (SELECT
                     sum(uvcnt)                      AS uvcnt,
                     tochar(conntime, 'YYYY-MM-DD') AS conntime
                   FROM portal.bmtuvconn
                   GROUP BY
                     conntime) uv
                WHERE pv.conntime = uv.conntime
                      AND uv.conntime BETWEEN $1 AND $2
                ORDER BY pv.conntime DESC

SELECT
                  pvcnt,
                  uvcnt,
                  uv.conntime
                FROM
                  (SELECT
                     sum(pvcnt)                      AS pvcnt,
                     tochar(conntime, 'YYYY-MM-DD') AS conntime
                   FROM portal.bmtpvconn
                   GROUP BY conntime) pv,
                  (SELECT
                     sum(uvcnt)                      AS uvcnt,
                     tochar(conntime, 'YYYY-MM-DD') AS conntime
                   FROM portal.bmtuvconn
                   GROUP BY
                     conntime) uv
                WHERE pv.conntime = uv.conntime
                      AND uv.conntime BETWEEN $1 AND $2
                ORDER BY pv.conntime DESC

Written by@MuseKim

Muse Kim