时间戳

首页 > 解决方案 > 按天间隔选择 SUM 和 GROUP BY

oracle - 按天间隔选择 SUM 和 GROUP BY

问题描述

我在 Oracle 数据库中有这张表：

       date       |   column1   |  column2
01/05/2020 00:00  |     50      |     20
01/05/2020 00:15  |     60      |     30
01/05/2020 00:30  |     70      |     40
       ...        |             |
01/05/2020 23:45  |     80      |     50
02/05/2020 00:00  |     100     |     40
02/05/2020 00:15  |     110     |     35

我有这个 SELECT 脚本：

SELECT
    period,
    liquid    
FROM
    (
        SELECT
            ( nvl(SUM(m.column1), 0) - nvl(SUM(m.column2), 0) ) liquid,            
            TO_CHAR(trunc(m.date), 'dd/MM/YYYY') period
        FROM
            table m
        WHERE
            m.id_register IN (id_register)
            AND m.date BETWEEN TO_DATE('01/05/2020 00:15:00', 'dd/mm/yyyy hh24:mi:ss') AND TO_DATE('01/06/2020 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
        GROUP BY
            TO_CHAR(trunc(m.date), 'dd/MM/YYYY')
    ) vrdm

我需要在每天 00:15 到 00:00（第二天）之间的时间间隔内对全天寄存器进行求和（差异列 1 和列 2）。怎么做？

我需要的结果是这样的：

  period   |  liquid
01/05/2020 |   3000     -> SUM(all differences between column1 and column2 between 01/05 00:15 and 02/05 00:00)
02/05/2020 |   4000     -> SUM(all differences between column1 and column2 between 02/05 00:15 and 03/05 00:00)
03/05/2020 |   3500     -> SUM(all differences between column1 and column2 between 03/05 00:15 and 04/05 00:00)

标签： oracleselectgroup-bytimestamp

解决方案

只需检查where子句中的时间部分：

SELECT to_char(trunc(m.dt), 'dd/MM/YYYY') period,
       nvl(SUM(m.column1), 0) - nvl(SUM(m.column2), 0) liquid
  FROM m
  WHERE to_char(m.dt, 'hh24:mi') >= '00:15' 
  GROUP BY trunc(m.dt)

小提琴手

推荐阅读