将数据从汇总更改为颗粒水平

发布于 2025-01-29 13:53:35 字数 800 浏览 2 评论 0原文

我的数据处于形式：

复制：

    DROP TABLE IF EXISTS SALARY;
    CREATE TEMP TABLE salary
    (
     Employee varchar(100),
     Salary1 numeric(38,12),
     Salary2 numeric(38,12)
    );
   INSERT INTO salary (Employee, Salary1 ,Salary2)
   VALUES ('A1',100,300),('A2',200,300),('A3',300,450),('A4',400,600);

我想均匀分配（因为我们将薪金汇总2天的数据汇总到1列中），并将其投入到每日级别的数据中：

因此，如果您看到员工A2-SUM的薪水为第三和4，则可能是300（从第二个表中获得150+150）。任何帮助/线索都值得赞赏。

原文

My data is in the form:

To reproduce:

    DROP TABLE IF EXISTS SALARY;
    CREATE TEMP TABLE salary
    (
     Employee varchar(100),
     Salary1 numeric(38,12),
     Salary2 numeric(38,12)
    );
   INSERT INTO salary (Employee, Salary1 ,Salary2)
   VALUES ('A1',100,300),('A2',200,300),('A3',300,450),('A4',400,600);

I want to divide it evenly (as we have data for 2 days of salary aggregated into 1 column) and cast it into a daily level data as below:

Hence, if you see for employee A2-Sum of salary for 3rd and 4th may would be 300 (150+150 from the 2nd table).
Any help/leads appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

从来不烧饼 2025-02-05 13:53:35

物质化的日历表随着所需的日期，询问。没有一个，a tally table 或cte（如下示例）是另一种方法。

DECLARE
      @StartDate date = '2022-05-01'
    , @DaysPerSalary int = 2;
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,salary1 AS (
        SELECT Employee, Salary1 / @DaysPerSalary AS Salary
        FROM SALARY
    )
    ,salary2 AS (
        SELECT Employee, Salary2 / @DaysPerSalary AS Salary
        FROM SALARY
    )
SELECT DATEADD(day, tally.num-1, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary1
WHERE tally.num <= @DaysPerSalary
UNION ALL
SELECT DATEADD(day, tally.num-1 + @DaysPerSalary, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary2
WHERE tally.num <= @DaysPerSalary
ORDER BY Employee, Salary;

A materialized calendar table with the desired dates will facilitate generating the dates needed for the query. Without one, a tally table or CTE (as in the below example) is an alternative method.

DECLARE
      @StartDate date = '2022-05-01'
    , @DaysPerSalary int = 2;
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num  FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,salary1 AS (
        SELECT Employee, Salary1 / @DaysPerSalary AS Salary
        FROM SALARY
    )
    ,salary2 AS (
        SELECT Employee, Salary2 / @DaysPerSalary AS Salary
        FROM SALARY
    )
SELECT DATEADD(day, tally.num-1, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary1
WHERE tally.num <= @DaysPerSalary
UNION ALL
SELECT DATEADD(day, tally.num-1 + @DaysPerSalary, @StartDate), Employee, Salary
FROM tally
CROSS JOIN salary2
WHERE tally.num <= @DaysPerSalary
ORDER BY Employee, Salary;

回复收藏 0 原文

~没有更多了~