date - sparql 获取两个日期之间的所有月份 SPARQL

Question

我正在处理 SPARQL 查询 在 TopBraid Composer 中获取每个月的数据质量规则数量。

我有开始日期和结束日期，但是 我需要获取开始日期和结束日期之间的所有月份，以便计算适用于该持续时间的数据质量规则。

我当前的查询和结果如下-:

SELECT *    
WHERE    
{    
   ?Rules CDE:Data_Quality_Rule_added_on ?Date1.    
   ?Rules CDE:Data_Quality_Removed_On ?Date2

   BIND(month(?Date1) AS ?Month1)    
   BIND(month(?Date2) AS ?Month2)    
   BIND(smf:duration("mon",?Date1,?Date2) AS ?dur)    
}    
LIMIT 5

| [Rules]                  | Date1      | Date2      | Month1 | Month2 | dur    
| CDE:Data_Quality_Rule_13 | 2016-01-28 | 2016-09-15 | 01     | 09     | 8   
| CDE:Data_Quality_Rule_16 | 2016-02-29 | 2016-08-08 | 02     | 08     | 5   
| CDE:Data_Quality_Rule_18 | 2016-05-15 | 2016-10-31 | 05     | 10     | 6   
| CDE:Data_Quality_Rule_4  | 2016-03-28 | 2016-07-02 | 03     | 07     | 3   
| CDE:Data_Quality_Rule_5  | 2016-02-02 | 2016-06-06 | 02     | 06     | 4   

我能够获得开始月份、结束月份和持续时间。但是我想在 SPARQL 中获取开始月份和结束月份之间的所有月份。最终结果是根据开始和结束日期获得数据质量规则数量的按月计数，如下所示:

| Months   | Number Of Data Quality Rules |
|  1       | 2                        
|  2       | 4                            
|  3       | 6                            
|  4       | 3                             
|  5       | 3                            
|  6       | 4                           
|  7       | 4                            
|  8       | 4                            
|  9       | 5                             
|  10      | 3                             
|  11      | 2                             
|  12      | 5                            

Answer 1

首先，一些要处理的数据有一些开始和结束日期的规则:

@prefix : <urn:ex:>
@prefix xsd: <http://www.w3.org/2001/XMLSchema#>

:rule1 :begin "2011-01-10T14:45:13.815-05:00"^^xsd:dateTime ;
       :end   "2011-06-10T14:45:13.815-05:00"^^xsd:dateTime .

:rule2 :begin "2011-04-10T14:45:13.815-05:00"^^xsd:dateTime ;
       :end   "2011-10-10T14:45:13.815-05:00"^^xsd:dateTime .

:rule3 :begin "2011-06-10T14:45:13.815-05:00"^^xsd:dateTime ;
       :end   "2011-11-10T14:45:13.815-05:00"^^xsd:dateTime .

然后您可以编写一个查询来获取每个规则处于事件状态的月份。如果您然后按月分组，并计算规则数量，您将获得每个月事件的规则数量:

prefix : <urn:ex:>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>

select ?rule ?month {
  #-- Specify the possible values for ?month
  #-- in advance.  These are just the numbers
  #-- one through twelve.
  values ?month { 1 2 3 4 5 6 7 8 9 10 11 12 }

  #-- Get the begin and end dates of that
  #-- you're interested in.  The way you
  #-- do this depends on the structure of
  #-- your data, of course.
  ?rule :begin ?begin ; :end ?end .

  #-- Then take only the values of ?month
  #-- that are between the beginning month
  #-- and the ending month.
  filter ( month(?begin) <= ?month && ?month <= month(?end) )
}

------------------
| rule   | month |
==================
| :rule1 | 1     |
| :rule1 | 2     |
| :rule1 | 3     |
| :rule1 | 4     |
| :rule1 | 5     |
| :rule1 | 6     |
| :rule2 | 4     |
| :rule2 | 5     |
| :rule2 | 6     |
| :rule2 | 7     |
| :rule2 | 8     |
| :rule2 | 9     |
| :rule2 | 10    |
| :rule3 | 6     |
| :rule3 | 7     |
| :rule3 | 8     |
| :rule3 | 9     |
| :rule3 | 10    |
| :rule3 | 11    |
------------------

现在您可以按月对这些结果进行分组，然后计算每个月的规则数:

prefix : <urn:ex:>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>

select ?month (count(distinct ?rule) as ?numRules) where {
  values ?month { 1 2 3 4 5 6 7 8 9 10 11 12 }

  ?rule :begin ?begin ; :end ?end .

  filter ( month(?begin) <= ?month && ?month <= month(?end) )
}
group by ?month

--------------------
| month | numRules |
====================
| 1     | 1        |
| 2     | 1        |
| 3     | 1        |
| 4     | 2        |
| 5     | 2        |
| 6     | 3        |
| 7     | 2        |
| 8     | 2        |
| 9     | 2        |
| 10    | 2        |
| 11    | 1        |
--------------------

结果中不包括第 12 个月，因为当时没有事件规则。如果要列出所有月份，可以将规则匹配部分设为可选:

prefix : <urn:ex:>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>

select ?month (count(distinct ?rule) as ?numRules) {
  values ?month { 1 2 3 4 5 6 7 8 9 10 11 12 }

  optional {
    ?rule :begin ?begin ; :end ?end .
    filter ( month(?begin) <= ?month && ?month <= month(?end) )
  }
}
group by ?month

--------------------
| month | numRules |
====================
| 1     | 1        |
| 2     | 1        |
| 3     | 1        |
| 4     | 2        |
| 5     | 2        |
| 6     | 3        |
| 7     | 2        |
| 8     | 2        |
| 9     | 2        |
| 10    | 2        |
| 11    | 1        |
| 12    | 0        |
--------------------