DEV Community: Judith-Data-Processing-Hacks

Query the Records Closest to the Specified Time Each Day — From SQL to SPL #43

Judith-Data-Processing-Hacks — Thu, 26 Jun 2025 01:26:40 +0000

Problem Description & Analysis:

A table in Oracle database has a column of datetime type, corresponding to multiple pieces of data per day:

Task: Now we need to find two records every day, one closest to 8am that day and one closest to 8pm that day.

Solution:

SPL code:

A1: Query the database through JDBC.

A2: Group by date, but do not aggregate for subsequent processing of each group of data.

A3: For each group of data, calculate the number of seconds between each record in the group and 8 am on the same day, take the absolute value, and find the record with the smallest absolute value; Calculate the record with the smallest absolute value of the number of seconds between 8 pm using the same method; Finally, merge the processed results of each group. The minp function is used to calculate the minimum record that meets the criteria.

✅ esProc SPL FREE Download — Free Trial Available, Download Now!

Generate Event Sequence Numbers within a Specified Time Interval — From SQL to SPL #42

Judith-Data-Processing-Hacks — Mon, 23 Jun 2025 05:43:29 +0000

Problem Description & Analysis:

A certain table in the MS SQL database has three fields: account, date and time of string type.

Task: Now we need to add a sequence number column Seq for the group. When a new event occurs for an account within one hour, Seq+1; If a new event occurs after one hour, reset Seq to 1.

Solution Hightlights:

When referencing set members in relative positions, SQL needs to write tedious window functions and often involves multiple layers of nesting; The logical judgment of sequence number calculation is also quite complex, involving multiple layers of case when forms, and the code is very cumbersome. The sequence numbers need to be accumulated within a specified interval, which requires grouping these intervals or embedding window functions in case when, which can be very convoluted.

It is also possible to reference set members at relative positions in a loop and make logical judgments, but SQL does not support loop structures and requires the use of stored procedures, making the structure more complex.

SPL supports a complete procedural syntax, which can handle complex business logic in loops and conveniently reference set members using relative positions.

A1: Use JDBC to query the database, spell out the calculated column DT of date and time type, and sort it by account and DT.

A2: Create a new two-dimensional table and add a new calculated column Seq. When the account number of the current record is the same as the previous record and the time interval is within one hour, Seq+1; Otherwise, reset Seq to 1. [-1] represents the previous record of relative position.

✅Experience esProc SPL FREE Download — Free Trial, No Hassle!✨

Row to Column Conversion Involving Uncertain Columns — From SQL to SPL #41

Judith-Data-Processing-Hacks — Fri, 20 Jun 2025 04:26:00 +0000

Problem Description & Analysis:

The query results of the Postgresql database return 6 columns, with the first column being the grouping column, the second column being the Value column within the group, and columns 3–6 being the Key columns within the group. Each record only has one Key column with a value, while the other Key columns are null. The position of the Key with a value for each record is uncertain.

Task: Now we need to keep the grouping column unchanged, convert the details within the group from rows to columns, convert the values of non-null Key columns to new column names, and convert the values of Value column to new column values.

Solution Highlights:

There are two ways to implement row to column conversion in SQL: one is through functions such as pivot and crosstab, and the other is to use the max group by statement for each new column. However, these two methods require writing new column names, which are the field values of the original table.

When the data changes, SQL also needs to change accordingly, which is very inflexible. If you want a set of code to be applicable to any data, you need to dynamically generate column names using stored procedures or high-level languages, and then execute the previous SQL, which makes the structure much more complex.

The row column conversion function of SPL can be used without writing field values, and a set of code is applicable to any data.

A1: Query the database through JDBC.

A2: Use the pivot function to convert this group of records from row to column. The first parameter is the grouping column, the second parameter is the column name of the Key, and the ifn function takes the first non-null member in the set. The third parameter is the Value column.

🚀 esProc SPL FREE Download — Try It Free, Download Now!

Search for Records Containing a Specified String Group in Multi Fields — From SQL to SPL #40

Judith-Data-Processing-Hacks — Wed, 18 Jun 2025 03:39:22 +0000

Problem Description & Analysis:

A table in an Oracle database has multiple string fields.

Task: Now we need to input a parameter that contains multiple strings separated by commas. We need to find the records in the table that contain all these strings in the fields, or those records where the set of fields is a superset of the parameter. For example, when the parameter argA=”street,John,Doe”, the calculation result is as follows:

Solution Highlights:

We can convert string parameters and each record into sets respectively, and filter out those records where the difference set of the two is an empty set.

But it is not easy to split string parameters into sets in SQL. Regular expressions and CONNECT BY functions are required, and the code is quite cumbersome. Some databases do not support this writing method and can only implement it using custom functions or stored procedures, which is even more troublesome. Converting a certain record into a set is also very cumbersome, usually requiring writing comparative judgments for each field, which is very tedious.

SPL provides directly usable functions that can easily convert string parameters and records into sets for intersection, union and difference operations.

A1: Query the database through JDBC.

A2: Split the parameter into a string set by commas. The split function splits a string using the specified delimiter, @c indicates that the delimiter is a comma.

A3: Filter out records where the difference between the parameter and the field set is an empty set, which is equivalent to finding records where the set of fields is a superset of the parameter.

🌟 Try esProc SPL for Free — esProc SPL FREE Download.

Add Records that Meet the Criteria before Each Group after Grouping — From SQL to SPL #39

Judith-Data-Processing-Hacks — Mon, 16 Jun 2025 03:46:49 +0000

Problem Description & Analysis:

In a certain view of the PostgreSQL database, the row_index field is an underscore separated string and also a grouping field. Some groups’ row_index can be divided into 3 parts, while others can be divided into 2 parts.

Task: Now, before each group of records where row_index can be divided into 3 parts, add the group of records where row_index can be divided into 2 parts, and modify row_index to the row_index of each group.

Solution Hightlights:

The records where row_index can be divided into three parts can be grouped by row_index, and then each group of records can be processed by merging the records where row_index can be divided into two parts before each group of records.

But after SQL grouping, it must aggregate immediately, and subsets cannot be kept to continue processing each group of records. This requires a detour to solve, using multi-layer nested window functions to bypass this problem, which is difficult to code.

SPL supports retaining subsets after grouping, allowing for continued processing of each group of records.

A1: Query the database through JDBC.

A2: Retrieve the records where row_index can be divided into two parts.

A3: Remove A2 from the complete data, which means getting records that row_index can be divided into three parts. Group these records while keeping the order unchanged. The symbol \ represents the difference set, and group@u indicates keeping the original order after grouping.

A4: Loop each group of data: Create a new two-dimensional table according to A2, change row_index to the row_index of this group, keep other fields unchanged, and merge the new two-dimensional table with the data of this group. Finally, merge the data of each group. The symbol | represents merging set members.

✅ Get Started with esProc SPL FREE Download — Try It Free!🆓

Count Distinct within Intervals in Order — From SQL to SPL #38

Judith-Data-Processing-Hacks — Thu, 12 Jun 2025 03:52:36 +0000

Problem Description & Analysis:

The Teradata database table has three fields: date, account, and the name of the product purchased by the account.

Task: Now we need to calculate how many types of products each account has purchased before each day, that is, the number of different products purchased from the beginning to the previous day.

Solutions:

Group by ACCOUNT, then by date, process each group in turn, get the interval from first group from to the current group, and perform deduplication and counting.

But SQL grouping must be accompanied by aggregation, and cannot retain grouped subsets for further processing; At this point, it is necessary to change the mindset, set various markers to transition, and write multi-layer nested statements to repeatedly traverse the data table in order to calculate.

SPL allows grouping to retain grouped subsets for further processing. SPL’s cross row references are also much simpler than window functions:

A1: Load data through JDBC.

A2: Group by account.

A3: Process each group of A2: Continue grouping by date, process daily data, get data of groups from the beginning to the previous day, union these groups, and calculate the quantity of different product names. Finally, union the processing results of each upper-level group. [: -1] represents the set from the first member to the previous member, and the function icount is used for unique count, i.e. count (distinct)

A4: Change the 0 in column 3 to null.

✨🔥 esProc SPL FREE Download — Free Trial Available, Try It Free Now!

Conditional Grouping — From SQL to SPL #37

Judith-Data-Processing-Hacks — Tue, 10 Jun 2025 02:45:22 +0000

Problem Description & Analysis:

A certain database table has multiple fields that can be grouped.

Task: Now we need to perform conditional grouping: group by the first two fields ID and SPLIT. If there are no duplicate DATEs within the group, keep the record with the latest date in this group and replace AMOUNT with the sum of AMOUNTs in this group; If there are duplicate dates within the group, group the records in this group again by CUST, while retaining the record with the latest date in the current group, and replace the AMOUNT with the sum of the AMOUNTs in the current group.

Code Comparisons:

SQL:

SELECT id, split,cust,date_column,
       CASE num_cust WHEN 1 THEN total_amount ELSE total_cust_amount  END AS amount
FROM   (
  SELECT t.*,
         COUNT(DISTINCT CASE rnk WHEN 1 THEN cust END)
           OVER (PARTITION BY id, split) AS num_cust
  FROM   (
    SELECT t.*,
           DENSE_RANK() OVER (PARTITION BY id, split ORDER BY date_column DESC) AS rnk,
           SUM(amount) OVER (PARTITION BY id, split) AS total_amount,
           SUM(amount) OVER (PARTITION BY id, split, cust) AS total_cust_amount
    FROM   test_table_mm t
  ) t
  WHERE  rnk = 1
)

SQL cannot retain grouped subsets and requires the use of multiple nested queries and multiple window functions for indirect implementation, resulting in complex code.

SPL: After SPL grouping, there is no need to aggregate immediately. The subsets can be kept for further calculation, and subsets can also continue to be grouped:

💭🧮 Try.DEMO

A1： Load data and sort it in reverse order by date.

A2： Group by the first two fields, but do not aggregate.

A3： Process each group of data: If the date is not duplicated, return the current group; otherwise, group the current group by CUST and return each group.

A4： Continue processing data of each group: Take the first record of the current group and replace AMOUNT with the sum of AMOUNTs of the current group.

🚀 Explore Your Data Like Never Before — Try It FREE Now! esProc SPL FREE Download.

Change Duplicate Contents to NULL — From SQL to SPL #36

Judith-Data-Processing-Hacks — Fri, 06 Jun 2025 02:37:45 +0000

Problem Description & Analysis:

The first two columns of a certain database table have duplicate values, such as the 2nd-3rd records and the 1st record being duplicated below.

Task: Now we need to change all duplicate values to null. In other words, after grouping by the first two fields (or one of them), only the first record in the group remains unchanged, and the first two fields of other records are changed to null.

Code Comparisons:

SQL：

SELECT CASE a_rn WHEN 1 THEN column_a END AS column_a,
       CASE b_rn WHEN 1 THEN column_b END AS column_b,
       column_c
FROM   (
  SELECT column_a,
         column_b,
         column_c,
         ROW_NUMBER() OVER (PARTITION BY column_a ORDER BY column_b, column_c)
           AS a_rn,
         ROW_NUMBER() OVER (PARTITION BY column_a, column_b ORDER BY column_c)
           AS b_rn
  FROM   table_name)

SQL does not have natural row numbers, nor does it have row numbers within a group, and can only generate them using window functions, making the code relatively cumbersome.

SPL: SPL has built-in row numbers, including row numbers within a group.

✨✅ Try.DEMO

A1： Load data.

A2： Group by Column_A, modify the data of each group, and when the row number within the group is not 1, change Column_A and Column_B to null; Finally, union the data from each group. # indicates the row number within the group.

If the data amount is not large, it is also possible not to group and union, and directly compare the Column_A of the current record with all previous Column_As. If the latter includes the former, then set Column_A and Column_B to null. The code is as follows:

\=A1.run(if(Column_A[:-1].contain(Column_A), (Column_A=Column_B=null)))

[: -1] represents the set from the first record to the previous record of the current record.

Powerful Data Processing Made Simple — Try It FREE 🚀👉🏻 esProc SPL FREE Download.

Get the Initial Date Using the Total — From SQL to SPL #35

Judith-Data-Processing-Hacks — Wed, 04 Jun 2025 03:10:38 +0000

Problem Description & Analysis:

A certain database table records the planned inbound quantity and total inventory after inbound on specific dates, such as the planned inbound quantity of 0.6 on February 26th, resulting in a total inventory of 3.

Task: Now, based on the given date, we need to use the total inventory to deduce the initial date, which is the day when there is zero or negative inventory. We need to add the daily consumption of UPDATED_QTY and the original inventory UPDATED_CUSTQTY. For example, given February 26th, it can be known that the original inventory of the day before inbound was 3–0.6=2.4; The previous date was February 23rd, and the original inventory on that day was 2.4–0.6=1.8; Until February 20th, the original inventory for that day was 0.

Code Comparisons:

SQL：

SELECT t.*,
       LEAST(
         GREATEST(
           COALESCE(
             SUM(
               CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
             ) OVER (PARTITION BY item, loc ORDER BY needdate DESC) - custqty,
             qty
           ),
           0
         ),
         qty
       ) AS updated_qty,
       CASE
       WHEN needdate > TRUNC(to_date('2024-02-26'))
       THEN NULL
       WHEN SUM(
              CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
            ) OVER (
              PARTITION BY item, loc
              ORDER BY needdate DESC
              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) >= custqty
       THEN NULL
       ELSE GREATEST(
              custqty
              - SUM(
                CASE WHEN needdate < TRUNC(to_date('2024-02-26')) + 1 THEN qty END
              ) OVER (PARTITION BY item, loc ORDER BY needdate DESC),
              0
           )
       END AS updated_custqty
FROM   table_name t

SQL requires multiple window functions to indirectly implement ordered calculation, and the code is complex and difficult to understand.

SPL: SPL can directly represent relative or absolute position:

👉🏻 Try.DEMO

A1： Load data, sort in reverse order by date, and add two calculated columns: the consumed inventory UPDATED_QTY, with the initial value being the inbound quantity; The original inventory UPDATED_CUSTQTY has an initial value of null.

A2： Filter out records before the specified date.

A3： Modify the record: If the current record is the first one, then original inventory=total inventory — received quantity; otherwise, original inventory=previous original inventory — received quantity, with the result rounded to one decimal place. If the original inventory is greater than or equal to 0, then the received quantity after consumption is 0. [-1] represents the previous record.

✨ 🔥 Free to Try, Powerful to Use — esProc SPL FREE Download.

Getting the Available Ranges from Two Tables — From SQL to SPL #34

Judith-Data-Processing-Hacks — Fri, 30 May 2025 02:29:29 +0000

Problem Description & Analysis:

There are two tables in a certain database. The original inventory table data_add stores multiple batches of inventory for multiple items. Each batch of inventory has a starting number START_NUM and an ending number END_NUM, representing the range of consecutive numbers.

The consumption table data_cons stores multiple batches of consumption for multiple items, each batch of consumption also has a continuous number range.

Task: Now we need to calculate the current inventory of each item, which is the original inventory minus consumption. Note that each batch is calculated separately, and the two consecutive batches are also calculated separately, such as the second and third records; The continuous numbering range of the original inventory may be consumed into discontinuous multi segment numbering, in which case multiple records need to be generated, with each record corresponding to a continuous numbering segment.

Code Comparisons:

SQL：

with merged_cons(item_id, start_num, end_num) AS (
    SELECT * FROM data_cons
    MATCH_RECOGNIZE (
        PARTITION BY item_id
        ORDER BY start_num, end_num
        MEASURES FIRST(start_num) AS start_num, LAST(end_num) AS end_num
        PATTERN( merged* strt )
        DEFINE
            merged AS MAX(end_num) + 1 >= NEXT(start_num)
    )
)
, intersections(id, item_id, start_before, end_before, start_after, end_after) AS (
    SELECT a.id, a.item_id, /*a.start_num AS add_start, a.end_num AS add_end, 
        b.start_num AS cons_start, b.end_num AS cons_end, */
        CASE WHEN a.start_num < b.start_num - 1 THEN a.start_num END AS start_before,
        CASE WHEN a.start_num < b.start_num - 1 THEN b.start_num - 1 END AS end_before,
        CASE WHEN b.end_num + 1 < a.end_num THEN b.end_num + 1 END AS start_after,
        CASE WHEN b.end_num + 1 < a.end_num THEN a.end_num END AS end_after
    FROM data_add a
    JOIN merged_cons b
        ON a.item_id = b.item_id AND LEAST(a.end_num, b.end_num) >= GREATEST(a.start_num, b.start_num)
)
SELECT item_id, start_before as start_num, end_before as end_num
FROM intersections WHERE start_before IS NOT NULL
UNION ALL
SELECT item_id, start_after as start_num, end_after as end_num
FROM intersections WHERE start_after IS NOT NULL
UNION ALL
SELECT item_id, start_num, end_num
FROM data_add d
WHERE NOT EXISTS(SELECT 1 FROM intersections i WHERE i.id = d.id);

SQL requires the use of MATCH_RECOGNIZE statements and multiple subqueries to indirectly implement set calculation, which is complex in code and difficult to understand.

SPL: SPL can use variables to represent sets and directly perform set related calculations:

🧩 Try.DEMO

A1-B1: Load data.

A2: Group the original inventory by item and convert each batch within the group into a continuous sequence small set, without aggregation. B2: Perform similar calculation to the consumption table and merge multiple small sets within the group into one large set. ~ indicates the current group or current member.

A3: Left join.

A4: Add calculated column diff: Calculate the difference set between each small set of the original inventory and the large set of the consumption table, group each difference set by condition, and assign the consecutive sequence to the same group. The function group is used for grouping, and by default is equivalence grouping, @i represents grouping by condition, and ~[-1] represents the previous member. Function merge@d merges ordered sets and calculate the difference set.

A5: Generate a record for each group in the diff field of each A4 record. The function news can expand a record into multiple records. ~.m(-1) represents the last member in the sequence ~.

✅👉🏻 Get Started with esProc SPL — esProc SPL FREE Download.

Total under Recursive Relationship — From SQL to SPL #33

Judith-Data-Processing-Hacks — Wed, 28 May 2025 02:22:31 +0000

Problem Description & Analysis:

A certain database has a ticket table and a work hour table. The ticket table stores the relationship between each ticket and its parent ticket, forming a self-association structure:

The work hour table stores multiple working hours corresponding to each ticket.
Click here to check out the Work Hour Table.

Task: Now we need to calculate the working hours for each ticket and recursively calculate the sum of the working hours for that ticket and all subordinate tickets, which is the total working hours.

Code Comparisons:

SQL：

WITH    CTE_TREE AS (
        SELECT  parentid AS parentid, ticketid AS children
        FROM    tickets t
        WHERE   parentID <> 0
        UNION
        SELECT  parentid, NULL
        FROM    tickets
        WHERE   parentID <> 0
        UNION
        SELECT  ticketid, NULL
        FROM    tickets
    )
    ,   CTE_TRAVERSE AS (
        SELECT  parentid AS mainId, children AS nextParent
        FROM    CTE_TREE
        UNION ALL
        SELECT  t.mainId, tree.children
        FROM    CTE_TREE tree
        INNER JOIN CTE_TRAVERSE t
            ON  t.nextParent = tree.parentid
        WHERE   tree.children <> ''
    )
    SELECT  t.MainID
    ,   SUM(CASE WHEN t.nextparent IS NULL THEN h.Hours END) AS Direct_hours
    ,   SUM(h.Hours) AS Total_hours
    FROM    CTE_TRAVERSE t
    INNER JOIN Hours h
        ON  h.ticketid = t.nextparent
        OR  (h.ticketid = t.mainID AND t.nextparent IS NULL)
    GROUP BY t.mainId

SQL requires multiple subqueries to implement self-association and recursive relationships, and the code is complex and difficult to understand.

SPL: SPL directly provides reference functions to establish self-associations and recursive functions to take all subordinate nodes:

🧩 Try.DEMO

A1： Load data and calculate the direct working hours for each ticket based on the ticket table.

A2： Set the parent ticket field of each record to point to the parent ticket record and establish a self-association relationship. The switch function can modify field values to records.

A3： Create a new two dimensional table, where the total working hours of the current ticket are equal to the direct working hours of the current ticket plus the sum of the working hours of all its subordinate sub tickets. The function nodes can recursively calculate all the subordinate records of a certain record.

Free to Try, Powerful to Use — esProc SPL FREE Download. ✨🚀

Converting JSON Data to Tabular in Snowflake — From SQL to SPL #32

Judith-Data-Processing-Hacks — Mon, 26 May 2025 03:20:44 +0000

Problem Description & Analysis:

The Snowflake database has a multi-layered JSON string:

{
  "enterprise": "xx",
  "genericTrap": "1",
  "pduBerEncoded": "xxx",
  "pduRawBytes": "xxxx",
  "peerAddress": "xx",
  "peerPort": "xx",
  "securityName": "xxx",
  "specificTrap": "1",
  "sysUpTime": "xxxx",
  "variables": [
    {
      "oid": "column_a",
      "type": "octetString",
      "value": "vala"
    },
    {
      "oid": "column_b",
      "type": "integer",
      "value": "valb"
    }
  ]
}

Task: Now we need to find the first layer field specificTrap as the grouping field; Find the first layer array variables, and extract the oid and value of each member as details.

Code Comparisons:

SQL：

with table_a(col) as (
    select
        parse_json(
            '{
  "enterprise": "xx",
  "genericTrap": "1",
  "pduBerEncoded": "xxx",
  "pduRawBytes": "xxxx",
  "peerAddress": "xx",
  "peerPort": "xx",
  "securityName": "xxx",
  "specificTrap": "1",
  "sysUpTime": "xxxx",
  "variables": [
    {
      "oid": "column_a",
      "type": "octetString",
      "value": "vala"
    },
    {
      "oid": "column_b",
      "type": "integer",
      "value": "valb"
    }
  ]
}'
        ) as variant
)
select
    any_value(specifictrap) specifictrap,
    max(case oid when 'column_a' then oid_val else null end)  column_a,
    max(case oid when 'column_b' then oid_val else null end)  column_b

from
    (
        select
            f.seq seq,
            col:specificTrap::VARCHAR specifictrap,
            f.value:oid::VARCHAR oid,
            f.value:value::VARCHAR oid_val
        from
            table_a,
            lateral FLATTEN(input => table_a.col:variables::ARRAY) f
    ) t
group by
    seq;

SQL does not support multiple layers of data and requires indirect implementation through nested queries and grouping aggregation, making the code difficult to understand.

SPL: SPL supports multi-layer data and allows direct access to multi-layer structures in an object-oriented manner:

✅ Try.DEMO

A1: Automatically parse built-in data type JSON, which can come from JDBC or parameters.

A2: Create a new two-dimensional table using the variables field values from A1, with OID and value retained, and specificTrap taken from A1.

Experience esProc SPL FREE Download — Free Trial, No Hassle!