跨月跨日查询

我的access日志数据库将时间存储为epoch，并将年月日提取为整数。此外，数据库的分区基于提取的y/m/d，我有35天的保留期。
如果我运行此查询：

select * 
from mydb 
where year in (2017, 2018) 
  and month in (12, 1) 
  and day in (31, 1)

在2018年1月29日，我将获得2017年12月31日和2018年1月1日的数据。
2018年1月5日，我将获取2017年12月1日、2017年12月31日和2018年1月1日的数据（不需要）
我也意识到我可以这样做：

select * 
from mydb 
where (year = 2017 and month = 12 and day = 31) 
   or (year = 2018 and month = 1 and day = 1)

但我真正想要的是：一个很好的方法来编写一个查询，其中我给出年、月、日作为起始值，然后给出第四个值（天数+），然后得到12/31/2017+5天的所有数据。
sql中是否有一种本地方法来实现这一点？我有一个庞大的数据集，如果我不指定日期，并且必须依赖于epoch来完成这项工作，那么查询将花费永远的时间。我对分区配置也没有影响。

使用impala作为dbms和sql方言，您将能够使用公共表表达式，但不能使用递归。此外，在插入参数时也可能出现问题。
下面是一个未经测试的建议，它将要求您找到一些功能替代品。首先，它生成一组行，其整数范围为0到999（在本例中）。如果需要，可以很容易地扩展行数。从这些行中，可以使用 date_add(timestamp startdate, int days/interval expression) 然后是 year(timestamp date) 以及 month(timestamp date) 以及 day(timestamp date) 请参见日期和时间函数创建与数据匹配所需的列。
总的来说，您应该能够构建一个通用的表表达式，该表达式包含涵盖所需范围的年、月、日列，并且可以内部联接到源表，从而实现日期范围筛选器。
下面的代码是使用t-sql（sqlserver）生成的，可以在这里进行测试。

-- produce a set of integers, adjust to suit needed number of these
;WITH
  cteDigits AS (
      SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      )
, cteTally AS (
      SELECT 
              d1s.digit 
            + d10s.digit * 10
            + d100s.digit * 100  /* add more like this as needed */
            -- + d1000s.digit * 1000  /* add more like this as needed */
            AS num
      FROM cteDigits d1s
      CROSS JOIN cteDigits d10s
      CROSS JOIN cteDigits d100s /* add more like this as needed */
      -- CROSS JOIN cteDigits d1000s /* add more like this as needed */
      )
, DateRange AS (
    select 
          num
        , dateadd(day,num,'20181227')        dt
        , year(dateadd(day,num,'20181227'))  yr
        , month(dateadd(day,num,'20181227')) mn
        , day(dateadd(day,num,'20181227'))   dy
    from cteTally
    where num < 10
    )
select

* 

from DateRange

我认为这些是上述函数调用的impala等价物：

, DateRange AS (
    select 
          num
        ,        date_add(to_timestamp('20181227','yyyyMMdd'),num)    dt
        ,  year( date_add(to_timestamp('20181227','yyyyMMdd'),num) )  yr
        , month( date_add(to_timestamp('20181227','yyyyMMdd'),num) )  mn
        ,   day( date_add(to_timestamp('20181227','yyyyMMdd'),num) )  dy
    from cteTally
    where num < 10

希望你能想出如何使用这些。最终目的是使用生成的日期范围，如下所示：

select * from mydb t
inner join DateRange on t.year = DateRange.yr and t.month = DateRange.mn and t.day = DateRange.dy

原岗位
在不知道要为哪个数据库提出解决方案的情况下，这里有一个使用sql server的建议：
这个建议涉及一个递归的公共表表达式，然后可以将它用作源数据的内部联接，从而将结果限制在一个日期范围内。

--Sql Server 2014 Express Edition
--https://rextester.com/l/sql_server_online_compiler

declare @yr as integer = 2018
declare @mn as integer = 12
declare @dy as integer = 27
declare @du as integer = 10

;with CTE as (
    select
           datefromparts(@yr, @mn, @dy) as dt
         , @yr as yr
         , @mn as mn
         , @dy as dy

    union all

    select
           dateadd(dd,1,cte.dt)
         , datepart(year,dateadd(dd,1,cte.dt)) 
         , datepart(month,dateadd(dd,1,cte.dt)) 
         , datepart(day,dateadd(dd,1,cte.dt)) 
    from cte
    where cte.dt < dateadd(dd,@du-1,datefromparts(@yr, @mn, @dy))
    )
select

* 

from cte

这将产生以下结果：

+----+---------------------+------+----+----+
|    |         dt          |  yr  | mn | dy |
+----+---------------------+------+----+----+
|  1 | 27.12.2018 00:00:00 | 2018 | 12 | 27 |
|  2 | 28.12.2018 00:00:00 | 2018 | 12 | 28 |
|  3 | 29.12.2018 00:00:00 | 2018 | 12 | 29 |
|  4 | 30.12.2018 00:00:00 | 2018 | 12 | 30 |
|  5 | 31.12.2018 00:00:00 | 2018 | 12 | 31 |
|  6 | 01.01.2019 00:00:00 | 2019 |  1 |  1 |
|  7 | 02.01.2019 00:00:00 | 2019 |  1 |  2 |
|  8 | 03.01.2019 00:00:00 | 2019 |  1 |  3 |
|  9 | 04.01.2019 00:00:00 | 2019 |  1 |  4 |
| 10 | 05.01.2019 00:00:00 | 2019 |  1 |  5 |
+----+---------------------+------+----+----+

以及：

select * from mydb t
inner join cte on t.year = cte.yr and t.month = cte.mn and t.day = cte.dy

可以使用整数表来代替递归公共表表达式（或者使用一组联合的select查询来生成一组整数），通常称为计数表。选择的方法将取决于所使用的dbms类型和版本。
同样，根据数据库的不同，将上面看到的结果持久化为临时表并向其添加索引可能更有效。

跨月跨日查询

1条答案

相关问题

热门标签

最新问答