江阴网站建设,江阴网站制作,江阴网站设计,江阴SEO优化,江阴小程序开发-江阴雨辰互联

  1. JustNews 首页
  2. questions

SQL Server sum over partition by - Stack Overflow

admin••questions•阅读0

I have a fact table and I am using sum over partition by on the column vacant. I want to sum vacant whe

I have a fact table and I am using sum over partition by on the column vacant. I want to sum vacant when vacant is above 0. I have the following query:

SELECT 
    f.[D_Building_Id],
    f.[D_Entity_Id],
    f.[Date],
    f.[D_Contract_Id],
    f.[Lead],
    f.[Vacant],
    CASE 
        WHEN f.Vacant <> '0' 
            THEN SUM(f.Vacant) OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date ASC) 
        ELSE 0 
    END AS Vacant_Leadtime
FROM 
    [fact] f  
LEFT JOIN 
    d_contract d_con ON d_con._Id = f.D_Contract_Id
WHERE 
    1 = 1

However I have the issue that when a new vacancy appears on the same combination of D_building_Id and D_Entity_Id as the previous vacancy that the sum continues with the previous one. But what I want that it starts a new sum when and does not continue with the previous sum over partition by.

See example date below as an example that you can use:

CREATE TABLE VacancyData 
(
    D_Building_id INT,
    D_Entity_Id INT,
    Date DATE,
    D_Contract_Id INT NULL,
    Lead INT NULL,
    Vacant INT,
    Vacant_Leadtime INT
)

INSERT INTO VacancyData (D_Building_id, D_Entity_Id, Date, D_Contract_Id, Lead, Vacant, Vacant_Leadtime) 
VALUES
(13492, 23406, '2024-10-24', NULL, NULL, 1, 148),
(13492, 23406, '2024-10-25', NULL, NULL, 1, 149),
(13492, 23406, '2024-10-26', NULL, NULL, 1, 150),
(13492, 23406, '2024-10-27', NULL, NULL, 1, 151),
(13492, 23406, '2024-10-28', NULL, NULL, 1, 152),
(13492, 23406, '2024-10-29', NULL, NULL, 1, 153),
(13492, 23406, '2024-10-30', NULL, NULL, 1, 154),
(13492, 23406, '2024-10-31', NULL, NULL, 1, 155),
(13492, 23406, '2024-11-01', NULL, NULL, 1, 156),
(13492, 23406, '2024-11-02', NULL, NULL, 1, 157),
(13492, 23406, '2024-11-03', NULL, NULL, 1, 158),
(13492, 23406, '2024-11-04', NULL, NULL, 1, 159),
(13492, 23406, '2024-11-05', NULL, NULL, 1, 160),
(13492, 23406, '2024-11-06', 7115,  0   ,1, 0),
(13492, 23406, '2024-11-07', 7126,  1   ,0, 0),
(13492, 23406, '2024-11-08', 7126,  2   ,0, 0),
(13492, 23406, '2024-11-09', 7126,  3   ,0, 0),
(13492, 23406, '2024-11-10', 7126,  4   ,0, 0),
(13492, 23406, '2024-11-11', 7126,  5   ,0, 0),
(13492, 23406, '2024-11-12', 7126,  6   ,0, 0),
(13492, 23406, '2024-11-14', 7126,  8   ,0, 0),
(13492, 23406, '2024-11-19', 7126,  13  ,0, 0),
(13492, 23406, '2024-11-20', 7126,  14  ,0, 0),
(13492, 23406, '2024-11-21', 7126,  15  ,0, 0),
(13492, 23406, '2024-11-25', 7126,  19  ,0, 0),
(13492, 23406, '2024-11-26', 7126,  20  ,0, 0),
(13492, 23406, '2024-11-27', 7126,  21  ,0, 0),
(13492, 23406, '2024-11-28', 7126,  22  ,0, 0),
(13492, 23406, '2024-11-29', 7126,  23  ,0, 0),
(13492, 23406, '2024-11-30', 7126,  24  ,0, 0),
(13492, 23406, '2024-12-01', 7126,  25  ,0, 0),
(13492, 23406, '2024-12-02', 7126,  26  ,0, 0),
(13492, 23406, '2024-12-06', 7126,  30  ,0, 0),
(13492, 23406, '2024-12-07', 7126,  31  ,0, 0),
(13492, 23406, '2024-12-08', 7126,  32  ,0, 0),
(13492, 23406, '2024-12-09', 7126,  33  ,0, 0),
(13492, 23406, '2024-12-10', 7126,  34  ,0, 0),
(13492, 23406, '2024-12-11', 7126,  35  ,0, 0),
(13492, 23406, '2024-12-12', 7126,  36  ,0, 0),
(13492, 23406, '2024-12-13', 7126,  37  ,0, 0),
(13492, 23406, '2024-12-14', 7126,  38  ,0, 0),
(13492, 23406, '2024-12-15', 7126,  39  ,0, 0),
(13492, 23406, '2024-12-16', 7126,  40  ,0, 0),
(13492, 23406, '2024-12-17', 7126,  41  ,0, 0),
(13492, 23406, '2024-12-18', 7126,  42  ,0, 0),
(13492, 23406, '2024-12-19', 7126,  43  ,0, 0),
(13492, 23406, '2024-12-20', NULL, NULL, 1, 161),
(13492, 23406, '2024-12-21', NULL, NULL, 1, 162),
(13492, 23406, '2024-12-22', NULL, NULL, 1, 163),
(13492, 23406, '2024-12-23', NULL, NULL, 1, 164),
(13492, 23406, '2024-12-24', NULL, NULL, 1, 165),
(13492, 23406, '2024-12-30', NULL, NULL, 1, 166),
(13492, 23406, '2025-01-01', NULL, NULL, 2, 167);

So the issue is that on 2024-12-20 the Vacant_Leadtime is 161, as it continues the sum of 2024-11-05. However, what I would like to see is that on the date 2024-12-20 and onwards the Vacant_Leadtime is 1 and starts a new sum. Meaning that I get the following output:

CREATE TABLE VacancyDataResult 
(
    D_Building_id INT,
    D_Entity_Id INT,
    Date DATE,
    D_Contract_Id INT NULL,
    Lead INT NULL,
    Vacant INT,
    Vacant_Leadtime INT
)

INSERT INTO VacancyDataResult (D_Building_id, D_Entity_Id, Date, D_Contract_Id, Lead, Vacant, Vacant_Leadtime) 
VALUES
(13492, 23406, '2024-10-24', NULL, NULL, 1, 148),
(13492, 23406, '2024-10-25', NULL, NULL, 1, 149),
(13492, 23406, '2024-10-26', NULL, NULL, 1, 150),
(13492, 23406, '2024-10-27', NULL, NULL, 1, 151),
(13492, 23406, '2024-10-28', NULL, NULL, 1, 152),
(13492, 23406, '2024-10-29', NULL, NULL, 1, 153),
(13492, 23406, '2024-10-30', NULL, NULL, 1, 154),
(13492, 23406, '2024-10-31', NULL, NULL, 1, 155),
(13492, 23406, '2024-11-01', NULL, NULL, 1, 156),
(13492, 23406, '2024-11-02', NULL, NULL, 1, 157),
(13492, 23406, '2024-11-03', NULL, NULL, 1, 158),
(13492, 23406, '2024-11-04', NULL, NULL, 1, 159),
(13492, 23406, '2024-11-05', NULL, NULL, 1, 160),
(13492, 23406, '2024-11-06', 7115,  0   ,1, 0),
(13492, 23406, '2024-11-07', 7126,  1   ,0, 0),
(13492, 23406, '2024-11-08', 7126,  2   ,0, 0),
(13492, 23406, '2024-11-09', 7126,  3   ,0, 0),
(13492, 23406, '2024-11-10', 7126,  4   ,0, 0),
(13492, 23406, '2024-11-11', 7126,  5   ,0, 0),
(13492, 23406, '2024-11-12', 7126,  6   ,0, 0),
(13492, 23406, '2024-11-14', 7126,  8   ,0, 0),
(13492, 23406, '2024-11-19', 7126,  13  ,0, 0),
(13492, 23406, '2024-11-20', 7126,  14  ,0, 0),
(13492, 23406, '2024-11-21', 7126,  15  ,0, 0),
(13492, 23406, '2024-11-25', 7126,  19  ,0, 0),
(13492, 23406, '2024-11-26', 7126,  20  ,0, 0),
(13492, 23406, '2024-11-27', 7126,  21  ,0, 0),
(13492, 23406, '2024-11-28', 7126,  22  ,0, 0),
(13492, 23406, '2024-11-29', 7126,  23  ,0, 0),
(13492, 23406, '2024-11-30', 7126,  24  ,0, 0),
(13492, 23406, '2024-12-01', 7126,  25  ,0, 0),
(13492, 23406, '2024-12-02', 7126,  26  ,0, 0),
(13492, 23406, '2024-12-06', 7126,  30  ,0, 0),
(13492, 23406, '2024-12-07', 7126,  31  ,0, 0),
(13492, 23406, '2024-12-08', 7126,  32  ,0, 0),
(13492, 23406, '2024-12-09', 7126,  33  ,0, 0),
(13492, 23406, '2024-12-10', 7126,  34  ,0, 0),
(13492, 23406, '2024-12-11', 7126,  35  ,0, 0),
(13492, 23406, '2024-12-12', 7126,  36  ,0, 0),
(13492, 23406, '2024-12-13', 7126,  37  ,0, 0),
(13492, 23406, '2024-12-14', 7126,  38  ,0, 0),
(13492, 23406, '2024-12-15', 7126,  39  ,0, 0),
(13492, 23406, '2024-12-16', 7126,  40  ,0, 0),
(13492, 23406, '2024-12-17', 7126,  41  ,0, 0),
(13492, 23406, '2024-12-18', 7126,  42  ,0, 0),
(13492, 23406, '2024-12-19', 7126,  43  ,0, 0),
(13492, 23406, '2024-12-20', NULL, NULL, 1, 1),
(13492, 23406, '2024-12-21', NULL, NULL, 1, 2),
(13492, 23406, '2024-12-22', NULL, NULL, 1, 3),
(13492, 23406, '2024-12-23', NULL, NULL, 1, 4),
(13492, 23406, '2024-12-24', NULL, NULL, 1, 5),
(13492, 23406, '2024-12-30', NULL, NULL, 1, 6),
(13492, 23406, '2025-01-01', NULL, NULL, 2, 7);

I am unsure on how to approach this issue, so if anybody has some suggestions that would be really helpful.

I have a fact table and I am using sum over partition by on the column vacant. I want to sum vacant when vacant is above 0. I have the following query:

SELECT 
    f.[D_Building_Id],
    f.[D_Entity_Id],
    f.[Date],
    f.[D_Contract_Id],
    f.[Lead],
    f.[Vacant],
    CASE 
        WHEN f.Vacant <> '0' 
            THEN SUM(f.Vacant) OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date ASC) 
        ELSE 0 
    END AS Vacant_Leadtime
FROM 
    [fact] f  
LEFT JOIN 
    d_contract d_con ON d_con._Id = f.D_Contract_Id
WHERE 
    1 = 1

However I have the issue that when a new vacancy appears on the same combination of D_building_Id and D_Entity_Id as the previous vacancy that the sum continues with the previous one. But what I want that it starts a new sum when and does not continue with the previous sum over partition by.

See example date below as an example that you can use:

CREATE TABLE VacancyData 
(
    D_Building_id INT,
    D_Entity_Id INT,
    Date DATE,
    D_Contract_Id INT NULL,
    Lead INT NULL,
    Vacant INT,
    Vacant_Leadtime INT
)

INSERT INTO VacancyData (D_Building_id, D_Entity_Id, Date, D_Contract_Id, Lead, Vacant, Vacant_Leadtime) 
VALUES
(13492, 23406, '2024-10-24', NULL, NULL, 1, 148),
(13492, 23406, '2024-10-25', NULL, NULL, 1, 149),
(13492, 23406, '2024-10-26', NULL, NULL, 1, 150),
(13492, 23406, '2024-10-27', NULL, NULL, 1, 151),
(13492, 23406, '2024-10-28', NULL, NULL, 1, 152),
(13492, 23406, '2024-10-29', NULL, NULL, 1, 153),
(13492, 23406, '2024-10-30', NULL, NULL, 1, 154),
(13492, 23406, '2024-10-31', NULL, NULL, 1, 155),
(13492, 23406, '2024-11-01', NULL, NULL, 1, 156),
(13492, 23406, '2024-11-02', NULL, NULL, 1, 157),
(13492, 23406, '2024-11-03', NULL, NULL, 1, 158),
(13492, 23406, '2024-11-04', NULL, NULL, 1, 159),
(13492, 23406, '2024-11-05', NULL, NULL, 1, 160),
(13492, 23406, '2024-11-06', 7115,  0   ,1, 0),
(13492, 23406, '2024-11-07', 7126,  1   ,0, 0),
(13492, 23406, '2024-11-08', 7126,  2   ,0, 0),
(13492, 23406, '2024-11-09', 7126,  3   ,0, 0),
(13492, 23406, '2024-11-10', 7126,  4   ,0, 0),
(13492, 23406, '2024-11-11', 7126,  5   ,0, 0),
(13492, 23406, '2024-11-12', 7126,  6   ,0, 0),
(13492, 23406, '2024-11-14', 7126,  8   ,0, 0),
(13492, 23406, '2024-11-19', 7126,  13  ,0, 0),
(13492, 23406, '2024-11-20', 7126,  14  ,0, 0),
(13492, 23406, '2024-11-21', 7126,  15  ,0, 0),
(13492, 23406, '2024-11-25', 7126,  19  ,0, 0),
(13492, 23406, '2024-11-26', 7126,  20  ,0, 0),
(13492, 23406, '2024-11-27', 7126,  21  ,0, 0),
(13492, 23406, '2024-11-28', 7126,  22  ,0, 0),
(13492, 23406, '2024-11-29', 7126,  23  ,0, 0),
(13492, 23406, '2024-11-30', 7126,  24  ,0, 0),
(13492, 23406, '2024-12-01', 7126,  25  ,0, 0),
(13492, 23406, '2024-12-02', 7126,  26  ,0, 0),
(13492, 23406, '2024-12-06', 7126,  30  ,0, 0),
(13492, 23406, '2024-12-07', 7126,  31  ,0, 0),
(13492, 23406, '2024-12-08', 7126,  32  ,0, 0),
(13492, 23406, '2024-12-09', 7126,  33  ,0, 0),
(13492, 23406, '2024-12-10', 7126,  34  ,0, 0),
(13492, 23406, '2024-12-11', 7126,  35  ,0, 0),
(13492, 23406, '2024-12-12', 7126,  36  ,0, 0),
(13492, 23406, '2024-12-13', 7126,  37  ,0, 0),
(13492, 23406, '2024-12-14', 7126,  38  ,0, 0),
(13492, 23406, '2024-12-15', 7126,  39  ,0, 0),
(13492, 23406, '2024-12-16', 7126,  40  ,0, 0),
(13492, 23406, '2024-12-17', 7126,  41  ,0, 0),
(13492, 23406, '2024-12-18', 7126,  42  ,0, 0),
(13492, 23406, '2024-12-19', 7126,  43  ,0, 0),
(13492, 23406, '2024-12-20', NULL, NULL, 1, 161),
(13492, 23406, '2024-12-21', NULL, NULL, 1, 162),
(13492, 23406, '2024-12-22', NULL, NULL, 1, 163),
(13492, 23406, '2024-12-23', NULL, NULL, 1, 164),
(13492, 23406, '2024-12-24', NULL, NULL, 1, 165),
(13492, 23406, '2024-12-30', NULL, NULL, 1, 166),
(13492, 23406, '2025-01-01', NULL, NULL, 2, 167);

So the issue is that on 2024-12-20 the Vacant_Leadtime is 161, as it continues the sum of 2024-11-05. However, what I would like to see is that on the date 2024-12-20 and onwards the Vacant_Leadtime is 1 and starts a new sum. Meaning that I get the following output:

CREATE TABLE VacancyDataResult 
(
    D_Building_id INT,
    D_Entity_Id INT,
    Date DATE,
    D_Contract_Id INT NULL,
    Lead INT NULL,
    Vacant INT,
    Vacant_Leadtime INT
)

INSERT INTO VacancyDataResult (D_Building_id, D_Entity_Id, Date, D_Contract_Id, Lead, Vacant, Vacant_Leadtime) 
VALUES
(13492, 23406, '2024-10-24', NULL, NULL, 1, 148),
(13492, 23406, '2024-10-25', NULL, NULL, 1, 149),
(13492, 23406, '2024-10-26', NULL, NULL, 1, 150),
(13492, 23406, '2024-10-27', NULL, NULL, 1, 151),
(13492, 23406, '2024-10-28', NULL, NULL, 1, 152),
(13492, 23406, '2024-10-29', NULL, NULL, 1, 153),
(13492, 23406, '2024-10-30', NULL, NULL, 1, 154),
(13492, 23406, '2024-10-31', NULL, NULL, 1, 155),
(13492, 23406, '2024-11-01', NULL, NULL, 1, 156),
(13492, 23406, '2024-11-02', NULL, NULL, 1, 157),
(13492, 23406, '2024-11-03', NULL, NULL, 1, 158),
(13492, 23406, '2024-11-04', NULL, NULL, 1, 159),
(13492, 23406, '2024-11-05', NULL, NULL, 1, 160),
(13492, 23406, '2024-11-06', 7115,  0   ,1, 0),
(13492, 23406, '2024-11-07', 7126,  1   ,0, 0),
(13492, 23406, '2024-11-08', 7126,  2   ,0, 0),
(13492, 23406, '2024-11-09', 7126,  3   ,0, 0),
(13492, 23406, '2024-11-10', 7126,  4   ,0, 0),
(13492, 23406, '2024-11-11', 7126,  5   ,0, 0),
(13492, 23406, '2024-11-12', 7126,  6   ,0, 0),
(13492, 23406, '2024-11-14', 7126,  8   ,0, 0),
(13492, 23406, '2024-11-19', 7126,  13  ,0, 0),
(13492, 23406, '2024-11-20', 7126,  14  ,0, 0),
(13492, 23406, '2024-11-21', 7126,  15  ,0, 0),
(13492, 23406, '2024-11-25', 7126,  19  ,0, 0),
(13492, 23406, '2024-11-26', 7126,  20  ,0, 0),
(13492, 23406, '2024-11-27', 7126,  21  ,0, 0),
(13492, 23406, '2024-11-28', 7126,  22  ,0, 0),
(13492, 23406, '2024-11-29', 7126,  23  ,0, 0),
(13492, 23406, '2024-11-30', 7126,  24  ,0, 0),
(13492, 23406, '2024-12-01', 7126,  25  ,0, 0),
(13492, 23406, '2024-12-02', 7126,  26  ,0, 0),
(13492, 23406, '2024-12-06', 7126,  30  ,0, 0),
(13492, 23406, '2024-12-07', 7126,  31  ,0, 0),
(13492, 23406, '2024-12-08', 7126,  32  ,0, 0),
(13492, 23406, '2024-12-09', 7126,  33  ,0, 0),
(13492, 23406, '2024-12-10', 7126,  34  ,0, 0),
(13492, 23406, '2024-12-11', 7126,  35  ,0, 0),
(13492, 23406, '2024-12-12', 7126,  36  ,0, 0),
(13492, 23406, '2024-12-13', 7126,  37  ,0, 0),
(13492, 23406, '2024-12-14', 7126,  38  ,0, 0),
(13492, 23406, '2024-12-15', 7126,  39  ,0, 0),
(13492, 23406, '2024-12-16', 7126,  40  ,0, 0),
(13492, 23406, '2024-12-17', 7126,  41  ,0, 0),
(13492, 23406, '2024-12-18', 7126,  42  ,0, 0),
(13492, 23406, '2024-12-19', 7126,  43  ,0, 0),
(13492, 23406, '2024-12-20', NULL, NULL, 1, 1),
(13492, 23406, '2024-12-21', NULL, NULL, 1, 2),
(13492, 23406, '2024-12-22', NULL, NULL, 1, 3),
(13492, 23406, '2024-12-23', NULL, NULL, 1, 4),
(13492, 23406, '2024-12-24', NULL, NULL, 1, 5),
(13492, 23406, '2024-12-30', NULL, NULL, 1, 6),
(13492, 23406, '2025-01-01', NULL, NULL, 2, 7);

I am unsure on how to approach this issue, so if anybody has some suggestions that would be really helpful.

  • sql
  • sql-server
  • sum
  • partition
Share Improve this question edited Mar 4 at 7:59 Frans asked Mar 3 at 7:35 FransFrans 214 bronze badges 4
  • Please edit your question, and show clearly the sample input data, along with the current and expected output. Do not include images, use text. – Tim Biegeleisen Commented Mar 3 at 8:17
  • SSMS is just an IDE (like) application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? Tagging [ssms] for your data engine is like tagging [visual-studio] for your programming language; it doesn't actually really tell us anything. – Thom A Commented Mar 3 at 9:29
  • Column leegstand doesn't seem to exist - did you perhaps translate it but not update all references? – Dale K Commented Mar 3 at 18:44
  • stackoverflow/help/someone-answers – Dale K Commented Mar 3 at 18:45
Add a comment  | 

2 Answers 2

Reset to default 2

Your Contract_ID have sequence (by date)

(null),(7115),(7126),(null)

Calculation on (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date) AS Vacant_Leadtime combines first and next (all) null's to one.

If you still want to distinguish between these sets of rows, mark the transitions as breaks (islands), count the island number, and add the island number to the partition.

See example.
I will use D_Contract_Id instead of Contractnr.

with gaps as(
 select *
    ,sum(isGap)over(partition by D_Building_Id,D_Entity_Id  order by Date) gapN
 from(
  select *
    ,case when coalesce(lag(D_Contract_Id)over(partition by D_Building_Id,D_Entity_Id   order by Date),0)
        =coalesce(D_Contract_Id,0) then 0
     else 1
     end isGap
  from VacancyData f
 )x
)
SELECT f.[D_Building_Id],f.[D_Entity_Id],f.[Date]
      ,f.[D_Contract_Id],f.[Lead],f.[Vacant]
      ,CASE WHEN f.Vacant <> '0' THEN 
          SUM(f.Vacant) OVER (PARTITION BY f.D_Entity_Id, f.D_Contract_Id,gapN ORDER BY f.Date ASC) 
       ELSE 0 
       END AS Vacant_Leadtime
      ,isGap,gapN
  FROM [gaps] f  
  WHERE 1=1
order by date

Fiddle

I think that your intention was to sum the CASE expression:

SUM(CASE WHEN f.Vacant <> '0' THEN f.Vacant ELSE 0 END)
    OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date) AS Vacant_Leadtime

Full updated query:

SELECT f.[D_Building_Id],
       f.[D_Entity_Id],
       f.[Date],
       f.[D_Contract_Id],
       f.[Lead],
       f.[Vacant],
SUM(CASE WHEN f.Vacant <> '0' THEN f.Vacant ELSE 0 END)
    OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date) AS Vacant_Leadtime
FROM [fact] f  
LEFT JOIN d_contract d_con
    ON d_con._Id = f.D_Contract_Id;

发布者：admin，转转请注明出处：http://www.yc00.com/questions/1745105477a4611529.html

adminadmin
0

相关推荐

  • SQL Server sum over partition by - Stack Overflowquestions

    SQL Server sum over partition by - Stack Overflow

    I have a fact table and I am using sum over partition by on the column vacant. I want to sum vacant whe

    adminadmin
    13小时前
    10

发表回复

评论列表（0条）

  • 暂无评论

联系我们

400-800-8888

在线咨询： QQ交谈

邮件：admin@example.com

工作时间：周一至周五，9:30-18:30，节假日休息

关注微信