SQL从“每月记录”转换为“从记录/直到”

问题描述：

我们存储每月员工的值（例如兼职百分比）的数据库：SQL从“每月记录”转换为“从记录/直到”

+-----+------+-------+----------+ 
| emp | year | month | parttime | 
+-----+------+-------+----------+ 
| 1 | 2015 |  1 |  100 | 
| 1 | 2015 |  2 |  100 | 
| 1 | 2015 |  3 |  100 | 
| 1 | 2015 |  4 |  100 | 
| 2 | 2015 |  1 |  80 | 
| 2 | 2015 |  2 |  100 | 
| 2 | 2015 |  3 |  100 | 
| 2 | 2015 |  4 |  80 | 
| 3 | 2015 |  1 |  60 | 
| 3 | 2015 |  2 |  60 | 
| 3 | 2015 |  3 |  80 | 
| 3 | 2015 |  4 |  100 | 
+-----+------+-------+----------+

报告目的我需要在显示值从一个/直到形式：

+-----+---------+---------+----------+ 
| emp | from | to | parttime | 
+-----+---------+---------+----------+ 
| 1 | 2015.01 | 2015.04 |  100 | 
| 2 | 2015.01 | 2015.01 |  80 | 
| 2 | 2015.02 | 2015.03 |  100 | 
| 2 | 2015.04 | 2015.04 |  80 | 
| 3 | 2015.01 | 2015.02 |  60 | 
| 3 | 2015.03 | 2015.03 |  80 | 
| 3 | 2015.04 | 2015.04 |  100 | 
+-----+---------+---------+----------+

我的第一次尝试是用简单的最小/最大的方法来解决它。但是员工nr。 2有点棘手，经常性值为80.

任何想法/示例？数据库基于db/2或microsoft。

感谢

菲利普

答

，如果你的数据库存储一个完整的日期，而不是仅仅在年/月的（或至少等效复合型）这会更容易些。或者，如果你能在原来的基础数据进行操作：那我专门使用

SELECT emp, partTime, MIN(monthStart) AS monthStart, MAX(monthNext) AS monthEnd 
FROM (SELECT emp, partTime, 
      DATEADD(month, month - 1, DATEADD(year, year - 1, CAST('00010101' AS DATE))) AS monthStart, 
      DATEADD(month, month, DATEADD(year, year - 1, CAST('00010101' AS DATE))) AS monthNext, 
      ROW_NUMBER() OVER(PARTITION BY emp ORDER BY year, month) - 
      ROW_NUMBER() OVER(PARTITION BY emp, partTime ORDER BY year, month) AS groupId 
     FROM Monthly_Hours) AS Grouping 
GROUP BY emp, partTime, groupId 
ORDER BY emp, monthStart

SQL Fiddle Example

注独家上限的范围内。日期/时间/时间戳类型，像所有正面的连续范围类型（除了明确的整数计数之外的任何东西）都应该始终用这种方式来解决（它使得推理和查询它们变得更容易）。

这个答案稍微有缺陷，因为缺失的月份没有直接报告（不显示为0） - 如有必要，有方法可以纠正此错误，但需要更多的工作。

我同意你关于存储数据。但我不控制数据库，我只需要使用给我的东西。好：我们可以确保数据中没有空白。所以我的问题完美的解决方案！ – Philipp

我不认为这个查询正常工作，因为你自己的小提琴已经显示（emp 4和parttime 80缺少记录）。这里有一个更改样本数据的小提琴，以更强烈地说明这一点：http://sqlfiddle.com/#!6/35eaa/1。 –

@Thorsten - 你是对的，虽然这是由于他的源数据没有差距（这导致这个问题）的事实缓解。可能是可以修复的，但目前还不能处理。 –

答

我测试过上给出您的样本数据的Postgres这个解决办法，但我几乎可以肯定，这将在DB2工作。它可能需要一些小的更改，但不确定。

要逐步了解它是如何工作的，你可以从执行最内层的程序块开始。

SELECT 
    emp, 
    (year||'.'||CASE WHEN length(min_month::text) = 1 THEN '0'||min_month::text ELSE min_month::text END) AS from, 
    (year||'.'||CASE WHEN length(max_month::text) = 1 THEN '0'||max_month::text ELSE max_month::text END) AS to, 
    parttime 
FROM(
    SELECT 
     emp, 
     year, 
     parttime, 
     first_different, 
     min(month) AS min_month, 
     max(month) AS max_month 
    FROM( 
     SELECT 
      a.*, 
      b.* 
     FROM(
      SELECT * 
      FROM tablename 
      ) a, 
      LATERAL 
      (
      SELECT 
       min(CASE WHEN a.parttime IS DISTINCT FROM b.parttime THEN b.month END) AS first_different 
      FROM 
       tablename b 
      WHERE 
       a.emp = b.emp 
       AND a.year = b.year 
       AND a.month < b.month 
      ) b 
     ) foo 
    GROUP BY 1,2,3,4 
    ORDER BY 1 
    ) goo 
ORDER BY 1,2;

结果：

emp | from | to | parttime 
-----+---------+---------+---------- 
    1 | 2015.01 | 2015.04 |  100 
    2 | 2015.01 | 2015.01 |  80 
    2 | 2015.02 | 2015.03 |  100 
    2 | 2015.04 | 2015.04 |  80 
    3 | 2015.01 | 2015.02 |  60 
    3 | 2015.03 | 2015.03 |  80 
    3 | 2015.04 | 2015.04 |  100

请不要使用隐式连接语法（逗号分隔的'FROM'子句）。做'LEFT（OUTER）JOIN'是很困难的，也是不可能的，而且很容易忘记一个条件并把它变成一个完整的笛卡尔积。始终明确列出联合，并在关联中尽可能多地添加相关条件。 –

这对DB/2也很好，谢谢！第一次使用“横向”加入！ – Philipp

@ Clockwork-Muse它被称为'LATERAL subquery'，它是一个有效的语法@Philipp很高兴知道它的工作原理:)请考虑投票答案。 –

答

这就是所谓的差距和问题群岛。为它的一个快速的解决方案：

DECLARE @Employee TABLE 
(emp int, year int, month int, parttime int) 

INSERT INTO @Employee 
VALUES 
(1, 2015, 1, 100), 
(1, 2015, 2, 100), 
(1, 2015, 3, 100), 
(1, 2015, 4, 100), 
(2, 2015, 1, 80), 
(2, 2015, 2, 100), 
(2, 2015, 3, 100), 
(2, 2015, 4, 80), 
(3, 2015, 1, 60), 
(3, 2015, 2, 60), 
(3, 2015, 3, 80), 
(3, 2015, 4, 100) 


;WITH cte 
AS 
(
    SELECT * 
     ,e.[month] - ROW_NUMBER() OVER (ORDER BY e.emp, e.[parttime]) AS Grp 
    FROM @Employee e 
) 
SELECT 
    emp, 
    CAST([year] AS varchar(50)) + '.' + CAST(MIN([month])AS varchar(50)) AS [from], 
    CAST([year] AS varchar(50)) + '.' + CAST(MAX([month])AS varchar(50)) AS [to], 
    parttime 
FROM cte 
GROUP BY emp, parttime, year, Grp 
ORDER BY emp, [from]

数据循环数年后不具有弹性。但是，差距和岛屿。 –

我不知道这叫做差距和群岛。谢谢你指出这个词。我同意Clockwork-Muse认为这应该考虑在一年内开始并在另一年结束的范围，但对此的修复很容易。一个非常直接的解决方案。 –

答

步骤一：检测其中用户或兼职变化发生（1 =变化，0 =相同的值作为最后一行）。您可以使用分析函数LAG执行此操作。

第二步：根据带有解析函数SUM的更改标记构建组。

第三步：在组中找到每个组最小和最大年份/月的记录。

 
+-----+------+-------+----------+-------+-------+ 
| emp | year | month | parttime | step1 | step2 | 
|  |  |  |   | chg | grp | 
+-----+------+-------+----------+-------+-------+ 
| 1 | 2015 |  1 |  100 |  1 |  1 | 
| 1 | 2015 |  2 |  100 |  0 |  1 | 
| 1 | 2015 |  3 |  100 |  0 |  1 | 
| 1 | 2015 |  4 |  100 |  0 |  1 | 
| 2 | 2015 |  1 |  80 |  1 |  2 | 
| 2 | 2015 |  2 |  100 |  1 |  3 | 
| 2 | 2015 |  3 |  100 |  0 |  3 | 
| 2 | 2015 |  4 |  80 |  1 |  4 | 
| 3 | 2015 |  1 |  60 |  1 |  5 | 
| 3 | 2015 |  2 |  60 |  0 |  5 | 
| 3 | 2015 |  3 |  80 |  1 |  6 | 
| 3 | 2015 |  4 |  100 |  1 |  7 | 
+-----+------+-------+----------+-------+-------+

select 
    emp, 
    min(format(year, '0000') + '.' + format(month, '00')) as from_month, 
    max(format(year, '0000') + '.' + format(month, '00')) as to_month, 
    parttime 
from 
(
    select 
    emp, year, month, parttime, 
    sum(chg) over (order by emp, year, month) as grp 
    from 
    (
    select 
     emp, year, month, parttime, 
     case when lag(emp) over (order by emp, year, month) = emp 
      and lag(parttime) lag(emp) over (order by emp, year, month) = parttime 
     then 0 
     else 1 
     end as chg 
    from mytable 
) changes 
) groups 
group by grp, emp, parttime 
order by grp;

SQL从“每月记录”转换为“从记录/直到”

相关推荐