SQL Runnnig多次使用相同的Query-Java 学习之路

我有一个查询，它给我两个日期之间的COUNT，start_date和end_date，并按不同的列分组 . 我有什么方法可以获得每天的COUNT吗？如同说start_date是date1而end_date是date5，所以我需要为'date1 to date2'，'date1 to date3'，然后'date1 to date4'，然后'date1 to date5'运行一次查询 . 如在基于开始和结束日期多次运行相同的查询？我的查询看起来像

Select COUNT(A), B, C, D
FROM TABLE 
WHERE CONDITION1 AND DATE BETWEEN start_date AND end_date
GROUP BY B, C, D

2 回答

Select COUNT(A), B, C, D,DATE 
FROM TABLE 
WHERE CONDITION1 AND DATE BETWEEN start_date AND end_date
GROUP BY B, C, D,DATE

在group by中添加 DATE

如果该字段是datetime，则使用 convert （ from @ta.speot.is : SQL Server 2005 does not have DATE as a type ）

Select COUNT(A), B, C, D,convert(date,DATETime) 
    FROM TABLE 
    WHERE CONDITION1 AND DATE BETWEEN start_date AND end_date
    GROUP BY B, C, D,convert(date,DATETime)

在group by中添加 DATE

Edited based on the OP :

declare increment int;
set increment  = 1
declare tempdate date;

set tempdate  = start_date  
while (tempdate  < end_date)


Select COUNT(A), B, C, D,DATE 
    FROM TABLE 
    WHERE CONDITION1 AND DATE BETWEEN start_date AND DATEADD(day,increment,  start_date )
    GROUP BY B, C, D,DATE 

set increment = increment   + 1
set tempdate   =  DATEADD(day,1,tempdate )
end

你必须使用循环并做一些像上面的事情

回复于 2024-04-26T22:19:36+08:00

我将展示如何使用Oracle实现此目标，然后如何将其应用于Vertica .

我首先编写一个查询来获取日期列表 . 像这样：

select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as begin_date
from all_objects
where rownum <= to_date('05-APR-2015','dd-mon-yyyy') - to_date('01-APR-2015','dd-mon-yyyy')+1;

01-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM
04-APR-15 12:00:00 AM
05-APR-15 12:00:00 AM

我对Vertica不太熟悉，但看起来这可以用这个查询实现：

SELECT ts::DATE
  FROM (SELECT '04/01/2015'::TIMESTAMP as tm
        UNION
        SELECT '04/05/2015'::TIMESTAMP as tm) as t
TIMESERIES ts as '1 Day' OVER (ORDER BY tm);

（来源：http://www.vertica-forums.com/viewtopic.php?t=1333）

然后，我使用笛卡尔/交叉连接到同一查询来创建日期范围：

select *
from (
    select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as begin_date
    from all_objects
    where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
) q1, (
    select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as end_date
    from all_objects
    where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
) q2
where begin_date <= end_date;

结果如下所示：

BEGIN_DATE              END_DATE
01-APR-15 12:00:00 AM   01-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   02-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   03-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   02-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   03-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM   03-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
04-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
04-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
05-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM

如果您不想要单日范围（例如，2015年4月1日 - 2015年4月1日），只需将begin_date <= end_date更改为begin_date <end_date .

完成后，您可以将整个查询加入到正在运行的查询中：

Select q.begin_date, q.end_date, t.B, t.C, t.D, count(t.A)
FROM tmp t, (
    select *
    from (
        select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as begin_date
        from all_objects
        where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
    ) q1, (
        select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as end_date
        from all_objects
        where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
    ) q2
    where begin_date <= end_date 
) q
where t.theDate between q.begin_date and q.end_date
group by q.begin_date, q.end_date, t.B, t.C, t.D
order by q.begin_date, q.end_date;

这是一个SQLFiddle：http://sqlfiddle.com/#!4/9628d/9

我希望有所帮助 .

回复于 2024-04-26T22:19:36+08:00

SQL Runnnig多次使用相同的Query

2 回答

相关问题