我有一个查询，给我渲染每分钟(当有一个渲染分钟)
select count(*) as "Total Rendered", to_char(r.request_dt, 'YYYY-MM-DD HH24:MI') as "ByMinute" from form_render r where r.form_type_id = 49
and r.request_dt >= to_timestamp('09-16-2015 08:00', 'mm-dd-yyyy hh24:mi')
group by to_char(r.request_dt, 'YYYY-MM-DD HH24:MI')
order by to_char(r.request_dt, 'YYYY-MM-DD HH24:MI') desc
Total rendering | ByMinute
19 2015-09-17 09:31
10 2015-09-17 09:30
1 2015-09-17 09:28
6 2015-09-17 09:27
18 2015-09-17 09:25
22 2015-09-17 09:24
12 2015-09-17 09:23
13 2015-09-17 09:21
22 2015-09-17 09:20
然而，我想要一行的所有时间(按分钟)，即使没有渲染(添加行时没有渲染发生的时间…09:22、09:26 09:29)
所以像这样的
Total rendering | ByMinute
19 2015-09-17 09:31
10 2015-09-17 09:30
0 2015-09-17 09:29
1 2015-09-17 09:28
6 2015-09-17 09:27
0 2015-09-17 09:26
18 2015-09-17 09:25
22 2015-09-17 09:24
12 2015-09-17 09:23
0 2015-09-17 09:22
13 2015-09-17 09:21
22 2015-09-17 09:20
任何帮助都是非常感谢的
Sean===========================================================================
9月20日尝试.....
===========================================================================
非常感谢您花时间整理这些东西。SQL绝对不是我的强项，我相信你可以告诉我!所以我已经修补了时间表和我的查询，但我得到一个错误(ORA01843:不是一个有效的月。)
当我单独运行查询时
select count(*)as "Total_Rendered" , to_char(r.request_dt, 'MM/DD/YYYY HH24:MI')as "ByMinute" from form_render r where r.form_type_id = 49
and r.request_dt >= to_timestamp('09/17/2015 09:11', 'mm/dd/yyyy hh24:mi') and r.request_dt <= to_timestamp('09/17/2015 09:18', 'mm/dd/yyyy hh24:mi')
group by to_char(r.request_dt, 'MM/DD/YYYY HH24:MI')
order by 2 desc
得到如下
TOTAL_RENDERED | ByMinute
---------------------------
4 | 09/17/2015 09:18
16 | 09/17/2015 09:17
4 | 09/17/2015 09:16
2 | 09/17/2015 09:11
运行minute_table查询
WITH min_date AS /* replace start and stop timestamp here */
(SELECT to_date('09/17/2015 09:11', 'MM/DD/YYYY hh24:mi') minute FROM dual
),
max_date AS
(SELECT to_date('09/17/2015 09:18', 'MM/DD/YYYY hh24:mi') minute FROM dual
),
minute_table AS
(SELECT
(SELECT minute FROM min_date
) + (rownum -1)/(24*60) AS by_minute
FROM dual
CONNECT BY level <=
(SELECT (24*60) *(
(SELECT minute FROM max_date
) -
(SELECT minute FROM min_date
))+1
FROM dual
)
)
select * from minute_table
得到如下....
9/17/2015 9:11:00 AM
9/17/2015 9:12:00 AM
9/17/2015 9:13:00 AM
9/17/2015 9:14:00 AM
9/17/2015 9:15:00 AM
9/17/2015 9:16:00 AM
9/17/2015 9:17:00 AM
9/17/2015 9:18:00 AM
到目前为止一切顺利。
当我将两个查询合并得到这个查询
WITH min_date AS /* replace start and stop timestamp here */
(SELECT to_date('09/17/2015 09:11', 'MM/DD/YYYY hh24:mi:ss') minute FROM dual
),
max_date AS
(SELECT to_date('09/17/2015 09:18', 'MM/DD/YYYY hh24:mi:ss') minute FROM dual
),
minute_table AS
(SELECT
(SELECT minute FROM min_date
) + (rownum -1)/(24*60) AS by_minute
FROM dual
CONNECT BY level <=
(SELECT (24*60) *(
(SELECT minute FROM max_date
) -
(SELECT minute FROM min_date
))+1
FROM dual
)
),
tbl AS
(
select count(*)as "Total_Rendered" , to_char(r.request_dt, 'MM/DD/YYYY HH24:MI')as "ByMinute" from form_render r where r.form_type_id = 49
and r.request_dt >= to_timestamp('09/17/2015 09:11', 'mm/dd/yyyy hh24:mi') and r.request_dt <= to_timestamp('09/17/2015 09:18', 'mm/dd/yyyy hh24:mi')
group by to_char(r.request_dt, 'MM/DD/YYYY HH24:MI')
order by 2 desc
)
SELECT minute_table.by_minute ,
NVL(tbl."Total_Rendered",'0') AS total_rendered
FROM minute_table
left OUTER JOIN tbl
ON tbl."ByMinute" = minute_table.by_minute
order by 1 desc
;
我得到一个错误ORA01843:不是一个有效的月份。
我不完全确定为什么会发生错误，但我相信它与我正在加入的列之间的格式差异有关?时间表上日期的格式与我的表格可能是原因，但我不确定。
时间表有9/17/2015上午9:11:00和我的查询是09/17/2015 09:11(注意月，秒和AM上缺少前导零)
任何帮助都是感激的。
再次感谢您的时间和专业知识....肖恩
稍微不同的解决方案，不依赖于12c，并且具有启动和停止分钟的"参数"。
WITH min_date AS /* replace start and stop timestamp here */
(SELECT to_date('2015-09-17 09:28','yyyy-mm-dd hh24:mi') minute FROM dual
),
max_date AS
(SELECT to_date('2015-09-17 09:31','yyyy-mm-dd hh24:mi') minute FROM dual
),
minute_table AS
(SELECT
(SELECT minute FROM min_date
) + (rownum -1)/(24*60) AS by_minute
FROM dual
CONNECT BY level <=
(SELECT (24*60) *(
(SELECT minute FROM max_date
) -
(SELECT minute FROM min_date
))+1
FROM dual
)
),
tbl AS
(SELECT 19 Total_Rendered,
to_date('2015-09-17 09:31','YYYY-MM-DD HH24:MI') By_Minute
FROM dual
UNION ALL
SELECT 1,to_date('2015-09-17 09:30','YYYY-MM-DD HH24:MI') FROM dual
UNION ALL
SELECT 19,to_date('2015-09-17 09:28','YYYY-MM-DD HH24:MI') FROM dual
)
SELECT minute_table.by_minute ,
NVL(tbl.total_rendered,'0') AS total_rendered
FROM minute_table
LEFT OUTER JOIN tbl
ON tbl.By_Minute = minute_table.by_minute
;
所以这是在Oracle 12c工作。但您必须更改它以供使用。
with tbl(Total_Rendered, ByMinute) as (
select 19,to_date('2015-09-17 09:31','YYYY-MM-DD HH24:MI') from dual union all
select 1,to_date('2015-09-17 09:30','YYYY-MM-DD HH24:MI') from dual union all
select 19,to_date('2015-09-17 09:28','YYYY-MM-DD HH24:MI') from dual )
select nvl(tbl.total_rendered,'0') as total_rendered,by_minute from
(select to_date('2015-09-17 09:28','YYYY-MM-DD HH24:MI') + (1/24/60) * column_value as by_minute from
TABLE( CAST( MULTISET( SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= 3 ) AS SYS.ODCINUMBERLIST
) )) time_range
left outer join
tbl
on tbl.ByMinute = time_range.by_minute
TOTAL_RENDERED BY_MINUTE
0 17-SEP-2015 09:29:00
1 17-SEP-2015 09:30:00
19 17-SEP-2015 09:31:00
我所做的是首先我使用一个临时表表，它将有数据作为你的输入。然后我从'17-SEP-2015 09:29:00' - '17-SEP-2015 09:31:00'生成时间戳来生成时间范围
现在我做了一个左外连接从这个time_range到缺少数据的表。对于不在表中的行，我打印0。
您可以使用这个查询并编辑它以获得time_range，并与您的数据连接以获得此输出