Query the Records Closest to the Specified Time Each Day — From SQL to SPL #43
#oracle#programming#sql#esprocspl
Judith-Data-Processing-Hacks

Judith-Data-Processing-Hacks @judith677

About: esProc SPL is a JVM-based programming language designed for structured data computation, serving as both a data analysis tool and an embedded computing engine. https://www.esproc.com/download-esproc

Joined:
Apr 20, 2022

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

Publish Date: Jun 26
6 1

Problem Description & Analysis:

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

source table

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

expected results

Solution:

SPL code:

esProc SPL

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!

Comments 1 total

Add comment