sql - 100 个逗号分隔的随机计数器位置值进行求和并显示最近 60 分钟的统计数据

Question

我正在尝试在 pentaho 仪表板上显示最近 60 分钟的统计信息(传递给 pentaho 的 oracle 11g 查询)。我的表中有列 (counter_buff)，其中包含 1000 个计数器位置，示例数据如下所示

counter_buff= '0,8,9,3,2,6,....15,62'  up to 1000 comma seperated values 

我正在尝试根据提供的固定位置从表中获取每个逗号分隔值并将它们相加，所以问题是如果我使用多个位置查询会变得更大和更慢，更慢的查询会导致仪表板上的延迟统计。

我创建了这个示例查询和结果:

查询:

{} 中显示的数字是计数器位置({16}、{24}..)，该位置将由用户定义。查询也使用 6 个联合，都像这样。

select * from
((SELECT MIN(to_char(TIMESTAMP,'HH24:MI:SS')) as TS,
         'SELL' as "STATUS",
         SUM((regexp_substr(counter_buff,'(.*?,){16}(.*?),', 1, 1,'', 2)) +
             (regexp_substr(counter_buff,'(.*?,){24}(.*?),', 1, 1,'', 2)) +
             (regexp_substr(counter_buff,'(.*?,){32}(.*?),', 1, 1,'', 2)) ......+
             (regexp_substr(counter_buff,'(.*?,){168}(.*?),', 1, 1,'', 2))) AS  "COUNTS"
    FROM (SELECT * FROM SHOPS
          order by TO_CHAR("TIMESTAMP",'YYYY-MM-DD HH24:MI:SS') desc) "SHOPS"
   where TOY_NAME = 'LION'
     and rownum <=60
   GROUP BY TO_CHAR("TIMESTAMP",'HH24:MI'))
  UNION ALL
 (SELECT MIN(to_char(TIMESTAMP,'HH24:MI:SS')) as TS,
         'RETURNED' as "STATUS",
         SUM((regexp_substr(counter_buff,'(.*?,){17}(.*?),', 1, 1,'', 2)) +
             (regexp_substr(counter_buff,'(.*?,){25}(.*?),', 1, 1,'', 2)) ..... +
             (regexp_substr(counter_buff,'(.*?,){153}(.*?),', 1, 1,'', 2)) +
             (regexp_substr(counter_buff,'(.*?,){161}(.*?),', 1, 1,'', 2)) +
             (regexp_substr(counter_buff,'(.*?,){169}(.*?),', 1, 1,'', 2))) AS "COUNTS"
    FROM (SELECT * FROM SHOPS
          order by TO_CHAR("TIMESTAMP",'YYYY-MM-DD HH24:MI:SS') desc) "SHOPS"
   where TOY_NAME = 'LION'
     and rownum <=60
   GROUP BY TO_CHAR("TIMESTAMP",'HH24:MI')) )
order by TS desc,STATUS desc

结果:

这只是一些结果行，结果将根据查询 rowid(为了减少空间我只粘贴了一半结果，但我使用的是最后 60 分钟的数据)

TS          STATUS       COUNTS
10:20:01    SELL         6
10:21:01    SELL         9
10:22:01    SELL         8
10:23:01    SELL         3

10:20:01    RETURNED     1
10:21:01    RETURNED     6  
10:22:01    RETURNED     7
10:23:01    RETURNED     2

我能够实现我想要的输出，但我想要更快和更小的查询选项。

我是 oracle 查询新手

Answer 1

您应该首先尽可能多地过滤数据，然后再做剩下的工作。此外，不需要 union，您可以在一个分组中执行所有操作，然后仅在需要时对结果进行逆透视。下面两个查询，应该有用。首先，您必须根据需要多次编写 regexp_substr:

/* sample data
with shops(toy_name, time_stamp, counter_buff) as (
    select 'LION', timestamp '2018-07-27 13:15:27', '0,8,9,3,2,6,15,62'    from dual union all
    select 'BEAR', timestamp '2018-07-27 13:44:06', '7,3,9,3,3,6,11,39'    from dual union all
    select 'LION', timestamp '2018-07-27 16:03:09', '7,3,151,44,3,6,11,39' from dual union all
    select 'LION', timestamp '2018-07-27 16:03:49', '7,3,11,4,3,6,11,39'   from dual )
-- end of data */
select to_char(time_stamp, 'hh24:mi') ts, 
       sum(regexp_substr(counter_buff,'(.*?,){2}(.*?),', 1, 1,'', 2) + 
           regexp_substr(counter_buff,'(.*?,){5}(.*?),', 1, 1,'', 2)) sell, 
       sum(regexp_substr(counter_buff,'(.*?,){3}(.*?),', 1, 1,'', 2) + 
           regexp_substr(counter_buff,'(.*?,){6}(.*?),', 1, 1,'', 2)) retu
  from (select time_stamp, counter_buff, row_number() over (order by time_stamp desc) rn 
          from shops where toy_name = 'LION') t
  where rn <= 60
  group by to_char(time_stamp, 'hh24:mi')

第二步，我将两个预定义数字表与您的数据连接起来。这些是接下来用作 regexp_substr 参数的“用户定义位置”。

with 
  /* sample data
  shops(toy_name, time_stamp, counter_buff) as (
    select 'LION', timestamp '2018-07-27 13:15:27', '0,8,9,3,2,6,15,62'    from dual union all
    select 'BEAR', timestamp '2018-07-27 13:44:06', '7,3,9,3,3,6,11,39'    from dual union all
    select 'LION', timestamp '2018-07-27 16:03:09', '7,3,151,44,3,6,11,39' from dual union all
    select 'LION', timestamp '2018-07-27 16:03:49', '7,3,11,4,3,6,11,39'   from dual ),
  */ -- end of sample data
  sell as (select rownum rn, column_value cs from table(sys.odcinumberlist(2, 5)) ),
  retu as (select rownum rn, column_value cr from table(sys.odcinumberlist(3, 6)) )
select *
  from (
    select sum(regexp_substr(counter_buff,'(.*?,){'||cs||'}(.*?),', 1, 1,'', 2)) sell, 
           sum(regexp_substr(counter_buff,'(.*?,){'||cr||'}(.*?),', 1, 1,'', 2)) retu, ts
      from (select to_char(time_stamp, 'HH24:MI') ts, counter_buff 
              from (select * from shops where toy_name = 'LION' order by time_stamp desc) 
              where rownum <= 60)
      cross join sell join retu using (rn) group by ts)
  unpivot (val for status in (sell, retu))

在这两个查询中，我假设 sell 位于位置 (2, 5)，returned 位于位置 (3, 6)。还可以针对 rownum 尝试使用 row_number() 并检查哪个对您来说更快。在这两种情况下，数据都只命中一次，这应该可以加快计算速度。