我的数据库中有过去两年的模型数据。我只想从最后两个季度的数据库表中选择那些模型。
假设Sysdate-> 29/07/2013(July)
我应该能够从低于季度的中检索模型
Q1-April,May,June(last quarter)
Q2-July,Aug,Sep(Including quarter)
以下查询需要如何更改?
Select model_id,model_name,Effective_date_from,effective_date_to
from model
where active='YES';
更新
对于Oracle:
FROM mytable t
JOIN ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'Q'),3) AS b
, ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) AS e
FROM DUAL
) dr
ON NOT ( t.effective_date_from >= dr.e OR t.effective_date_to < dr.b )
OR ( t.effective_date_from IS NULL AND t.effective_date_to IS NULL )
对于MySQL:
FROM mytable t
JOIN ( SELECT MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER AS b
, MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER AS e
) dr
ON NOT ( t.effective_date_from >= dr.e OR t.effective_date_to < dr.b )
OR ( t.effective_date_from IS NULL AND t.effective_date_to IS NULL )
在查看了slOppy的答案并再次查看问题后,我意识到您有两个日期列,effective_date_from
和effective_date_to
,并且我没有考虑到您可能想要检索行,其中from
和to
日期值之间的任何时间点在指定日期范围内(上一季度开始和下一季度开始之间)
为了简化(目前),我将只考虑生效日期为from < to
的情况。
可能的情况可以这样描述(相当粗略):竖线表示drb
和dre
(日期范围开始和结束)小于号表示"effective_date_from
"(edf
)大于号表示"effective_date_to
"(edt
)虚线表示edf
和edt
之间的"生效"日期
drb dre
<---> | | case 1: edt < drb
<---|----> | case 2: drb between edf and edt
<--|-------|----> case 3: edf < drb AND edt > dre
| <---> | case 4: edf > drb AND edt < dre
| <--|---> case 5: edf between drb and dre AND edt > dre
| | <---> case 6: edf > dre
| |
<---> | case e1: edt = drb
<--|-------> case e2: edf > drb AND edt = dre
<---> | case e3: edf = drb and edt < dre
<-------> case e4: edf = drb and edt = dre
<-------|--> case e5: edf = drb and edt > dre
| <---> case e6: edf > drb AND edt = dre
| <---> case e7: edf = dre
我认为您要求返回满足情况2到5以及边缘情况e1到e6的行,这是除情况1、6和e7之外的所有情况。
如果我们可以编写一个谓词来测试情况1和6,然后否定它,它应该会给我们你想要的。类似这样的东西:
处理考虑时间成分的日期时间:
WHERE NOT ( effective_date_to < drb OR effective_date_from >= dre )
(生成drb
和dre
(日期范围开始和日期范围结束)的表达式显示在我的原始答案中。)
为了返回effective_date_from
和effective_date_to
都为NULL的行,我们可以添加一个单独的条件来处理这种情况。
使用内联视图来提供这些表达式,我们可以得到答案顶部的SQL。
我之前搁置了一些奇怪的案例,比如effective_date_from > effective_date_to
;您必须弄清楚这些行是否有效,以及返回这些行的条件。
原始答案:
这可以在Oracle和MySQL中实现。我使用的方法是首先生成每个季度相对于当前日期的"开始日期"。我们可以在谓词中引用那些日期值(或返回日期值的表达式)。
要在MySQL中导出季度的"开始日期",我们可以使用QUARTER()
函数。(此函数从指定的DATE值返回1到4的整数值。结合YEAR
和MAKEDATE函数,我们可以从当前系统日期生成每个相对季度的"开始日期",如下所示:
SELECT MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER AS next_q
, MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-1 QUARTER AS this_q
, MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER AS prev_q
next_q this_q prev_q
---------- ---------- ------------
2013-10-01 2013-07-01 2013-04-01
一旦我们有了这些日期值,就很简单了。为了只获取特定日期列(以下示例中的mydate
)的值位于上一季度或当前季度内的行,我们只需将该日期列与上面的两个表达式进行比较:
WHERE mydate >= MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER
AND mydate < MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER
在Oracle中,方法是相同的。不同之处在于我们如何得出相对季度的开始日期。在Oracle中,我们可以使用以下表达式:
-- ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT ADD_MONTHS(TRUNC(SYSDATE,'Q'),3) AS next_q
, ADD_MONTHS(TRUNC(SYSDATE,'Q'),0) AS this_q
, ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) AS prev_q
FROM DUAL
NEXT_Q THIS_Q PREV_Q
---------- ---------- ------------
2013-10-01 2013-07-01 2013-04-01
因此,为了获得mydate
的值在当前或上一季度的行,我们将该列与其中两个表达式进行比较:
WHERE mydate >= ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3)
AND mydate < ADD_MONTHS(TRUNC(SYSDATE,'Q'),3)
(大于或等于上一季度的开始日期,小于下一季度的结束日期)。
使用这些"开始日期"的一种稍微不同的方法是使用内联视图来返回日期值;这为我们提供了一个可以测试的单独查询,并且我们可以为表达式分配列名。在这里,我使用别名dr
(日期范围)作为内联视图:
Oracle
FROM mytable t
JOIN ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'Q'),3) AS bd_next_qtr
, ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) AS bd_prev_qtr
FROM DUAL
) dr
ON t.mydate >= dr.bd_prev_qtr
AND t.mydate < dr.bd_next_qtr
MySQL
FROM mytable t
JOIN ( SELECT MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER AS bd_next_qtr
, MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER AS bd_prev_qtr
) dr
ON t.mydate >= dr.bd_prev_qtr
AND t.mydate < dr.bd_next_qtr
你尝试过吗:
SELECT
*
FROM
yourTable
WHERE
yourDate >= DATE_FORMAT( CURRENT_DATE - INTERVAL 3 MONTH, '%Y/%m/01' )
AND
yourDate < DATE_FORMAT( CURRENT_DATE- INTERVAL 1 MONTH, '%Y/%m/01');
我会得到你第一季度的开始日期(min_date)和第二季度的结束日期(max_date),以获得合适的范围,然后执行以下操作:
SELECT model_id,model_name,Effective_date_from,effective_date_to
FROM model
WHERE active='YES'
AND (Effective_date_from BETWEEN min_date AND max_date OR effective_date_to BETWEEN min_date AND max_date)
;
这应该会给你在这两个季度的某个时候活跃的模型。