如何在sql表中的组中填写缺少的日期 [英] How to fill missing dates by groups in a table in sql

问题描述

我想知道如何使用循环来填充缺省值为零的日期，基于sql中的开始/结束日期，以便我在每个组中具有连续的时间序列。我有两个问题。

1. 如何循环每个组？


2. 如何使用开始/每个组的结束日期会动态填写缺少的日期？

我的输入和预期输出如下所列。

输入：我有一张表A，如

  date价值grp_no 
 8/06/12 1 1 
 8/08/12 1 1 
 8/09/12 0 1 
 8/07/12 2 2 
 8/08/12 1 2 
 8/12/12 3 2 
  

我也有一张表B可以用来与A一起填写缺少的日期。

  date 
 ... 
 8/05/12 
 8/06/12 
 8/07/12 
 8/08/12 
 8/09/12 
 8 / 10/12 
 8/11/12 
 8/12/12 
 8/13/12 
 ... 
  / pre> 
 
 
如何使用A和B在sql中生成以下输出？ 
 
 
 
 输出： 
  grp_no 
 8/06/12 1 1 
 8/07/12 0 1 
 8/08/12 1 1 
 8/09/12 0 1 
 8 / 07/12 2 2 
 8/08/12 1 2 
 8/09/12 0 2 
 8/10/12 0 2 
 8/11/12 0 2 
 8/12/12 3 2 
  
请给我你的代码和建议。谢谢你提前!!! 
解决方案
你可以这样做没有循环
  SELECT p.date，COALESCE（a.value，0）value，p.grp_no 
 FROM 
（
 SELECT grp_no ，日期
 FROM 
（
 SELECT grp_no，MIN（date）min_date，MAX（date）max_date 
 FROM tableA 
 GROUP BY grp_no 
）q CROSS JOIN tableb b 
 WHERE b.date BETWEEN q.min_date AND q.max_date 
）p LEFT JOIN TableA a 
 ON p.grp_no = a.grp_no 
 AND p.date = a.date 
  
 最内层的子查询每组获取最小和最大日期。然后与 TableB 交叉连接可以在每个组的最小 - 最大范围内生成所有可能的日期。最后，外部选择使用 TableA 的外部连接，并使用 0 值列c $ c>  TableA 中缺少的日期。
 
 
 
输出：
 
 | DATE | VALUE | GRP_NO | 
 | ------------ | ------- | -------- | 
 | 2012-08-06 | 1 | 1 | 
 | 2012-08-07 | 0 | 1 | 
 | 2012-08-08 | 1 | 1 | 
 | 2012-08-09 | 0 | 1 | 
 | 2012-08-07 | 2 | 2 | 
 | 2012-08-08 | 1 | 2 | 
 | 2012-08-09 | 0 | 2 | 
 | 2012-08-10 | 0 | 2 | 
 | 2012-08-11 | 0 | 2 | 
 | 2012-08-12 | 3 | 2 | 
 
这里是  SQLFiddle  演示
 
I want to know how to use loops to fill in missing dates with value zero based on the start/end dates by groups in sql so that i have consecutive time series in each group. I have two questions.

how to loop for each group?
How to use start/end dates for each group to dynamically fill in missing dates?
My input and expected output are listed as below.

Input: I have a table A like
date     value      grp_no
8/06/12    1         1
8/08/12    1         1
8/09/12    0         1
8/07/12    2         2
8/08/12    1         2
8/12/12    3         2
Also I have a table B which can be used to left join with A to fill in missing dates.
date
...
8/05/12
8/06/12
8/07/12
8/08/12
8/09/12
8/10/12
8/11/12
8/12/12
8/13/12
...
How can I use A and B to generate the following output in sql? 

Output:
date     value      grp_no
8/06/12    1         1  
8/07/12    0         1
8/08/12    1         1
8/09/12    0         1
8/07/12    2         2
8/08/12    1         2
8/09/12    0         2
8/10/12    0         2
8/11/12    0         2
8/12/12    3         2
Please send me your code and suggestion. Thank you so much in advance!!!
解决方案
You can do it like this without loops
SELECT p.date, COALESCE(a.value, 0) value, p.grp_no
  FROM
(
  SELECT grp_no, date
    FROM
  (
    SELECT grp_no, MIN(date) min_date, MAX(date) max_date
      FROM tableA
     GROUP BY grp_no
  ) q CROSS JOIN tableb b 
   WHERE b.date BETWEEN q.min_date AND q.max_date
) p LEFT JOIN TableA a
    ON p.grp_no = a.grp_no 
   AND p.date = a.date
The innermost subquery grabs min and max dates per group. Then cross join with TableB produces all possible dates within the min-max range per group. And finally outer select uses outer join with TableA and fills value column with 0 for dates that are missing in TableA.

Output:
|       DATE | VALUE | GRP_NO |
|------------|-------|--------|
| 2012-08-06 |     1 |      1 |
| 2012-08-07 |     0 |      1 |
| 2012-08-08 |     1 |      1 |
| 2012-08-09 |     0 |      1 |
| 2012-08-07 |     2 |      2 |
| 2012-08-08 |     1 |      2 |
| 2012-08-09 |     0 |      2 |
| 2012-08-10 |     0 |      2 |
| 2012-08-11 |     0 |      2 |
| 2012-08-12 |     3 |      2 |
Here is SQLFiddle demo

                        
这篇关于如何在sql表中的组中填写缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！