Align the existing data to the corresponding position and fill in any missing data with 0：From SQL to SPL

The MySQL database has a sampling table, where each ITEM and CITY is a sampling task. Each sampling task includes 1 to 5 records, indicating that data was collected within 5 weeks. START_Y and START_W are the year and week when sampling started, while FIRST_USE_Y and FIRST_USE_W are the year and week when data was actually collected. The calculation rule for week numbers: Starting from January 1st, every 7 days counts as a week and accumulates sequentially. Now we need to expand each sampling task into 5 records (weeks), increasing sequentially from the year and week of sampling, including the weeks where data was actually collected and those where data was not collected. The former should be aligned to the corresponding position, while the latter has a VALUE of 0. SQL： WITH ItemCity As ( SELECT Item, City, MIN( DATEADD(day, Start_W7, DATEFROMPARTS(Start_Y, 1, 1)) ) As StartWeek FROM Data GROUP BY Item, City ), ItemCityWeeks As ( SELECT Item,City, StartWeek ,Year(StartWeek) As Start_Y,datepart(week, StartWeek)-1 As Start_W ,YEAR(DATEADD(day, Weeks.num7, StartWeek)) As First_Use_Y ,DATEPART(dayofyear, DATEADD(day, Weeks.num*7, StartWeek))/7 As First_Use_W FROM ItemCity CROSS JOIN ( VALUES (0), (1), (2), (3), (4)) Weeks(num) ) SELECT icw.Item, icw.City , icw.Start_Y, icw.Start_W, icw.First_Use_Y, icw.First_Use_W , coalesce(d.value, 0) as Value FROM ItemCityWeeks icw LEFT JOIN Data d ON d.Item = icw.Item AND d.City = icw.City and d.First_Use_Y = icw.First_Use_Y and d.First_Use_W = icw.First_Use_W ORDER BY Item, City DESC After SQL grouping, it must aggregate immediately. It cannot keep the grouped subsets and expand each subset into N records, and then simply align the VALUE using filtering methods. It can only solve it by taking a detour: First group and aggregate and then expand, and cross multiplication should be used for expansion. When aligning VALUE, the indirect implementation of multi field join is necessary, and the structure is very complex and the code is also verbose. SPL code is much simpler and easier to understand: A1： Load data. A2： Group by task, and it can retain the grouped subsets without aggregation. A3： Expand each group of data directly into 5 records, calculate the week number according to the rules, and simply filter out the VALUE corresponding to the current week number. Function pdate@y returns the first day of the year in which the date is located, ifn returns the first non-null member, select@1 filters out the first record that meets the criteria. SPL is open source and free, please try it! Open source address

Feb 6, 2025 - 10:12

Align the existing data to the corresponding position and fill in any missing data with 0：From SQL to SPL

The MySQL database has a sampling table, where each ITEM and CITY is a sampling task. Each sampling task includes 1 to 5 records, indicating that data was collected within 5 weeks. START_Y and START_W are the year and week when sampling started, while FIRST_USE_Y and FIRST_USE_W are the year and week when data was actually collected. The calculation rule for week numbers: Starting from January 1st, every 7 days counts as a week and accumulates sequentially.

Now we need to expand each sampling task into 5 records (weeks), increasing sequentially from the year and week of sampling, including the weeks where data was actually collected and those where data was not collected. The former should be aligned to the corresponding position, while the latter has a VALUE of 0.

SQL：

WITH ItemCity As (
    SELECT Item, City, MIN(  DATEADD(day, Start_W*7, DATEFROMPARTS(Start_Y, 1, 1)) ) As StartWeek
    FROM Data
    GROUP BY Item, City
), 
ItemCityWeeks As (
   SELECT Item,City, StartWeek
       ,Year(StartWeek) As Start_Y,datepart(week, StartWeek)-1 As Start_W
       ,YEAR(DATEADD(day, Weeks.num*7, StartWeek)) As First_Use_Y
       ,DATEPART(dayofyear, DATEADD(day, Weeks.num*7, StartWeek))/7 As First_Use_W
   FROM ItemCity
   CROSS JOIN ( VALUES (0), (1), (2), (3), (4)) Weeks(num)
)
SELECT icw.Item, icw.City
      , icw.Start_Y, icw.Start_W, icw.First_Use_Y, icw.First_Use_W
      , coalesce(d.value, 0) as Value
FROM ItemCityWeeks icw
LEFT JOIN Data d ON d.Item = icw.Item AND d.City = icw.City 
      and d.First_Use_Y = icw.First_Use_Y and d.First_Use_W = icw.First_Use_W
ORDER BY Item, City DESC

After SQL grouping, it must aggregate immediately. It cannot keep the grouped subsets and expand each subset into N records, and then simply align the VALUE using filtering methods. It can only solve it by taking a detour: First group and aggregate and then expand, and cross multiplication should be used for expansion. When aligning VALUE, the indirect implementation of multi field join is necessary, and the structure is very complex and the code is also verbose.

SPL code is much simpler and easier to understand:

A1： Load data.

A2： Group by task, and it can retain the grouped subsets without aggregation.

A3： Expand each group of data directly into 5 records, calculate the week number according to the rules, and simply filter out the VALUE corresponding to the current week number. Function pdate@y returns the first day of the year in which the date is located, ifn returns the first non-null member, select@1 filters out the first record that meets the criteria.

SPL is open source and free, please try it!

Open source address