SQL: Perform statistics at different granularities based on the time span of the data #85

A certain view in MS SQL will generate data with different time spans. When the time span is less than 30 days:

Grouping by branchId and day is required, and the total price should be summed up as follows:

When the time span is greater than or equal to 30 days and less than 365 days:

Then group by branchId and month, and sum up the total price. Note: The format remains unchanged, and the output time field is taken as the first day of the current month.

When the time span is greater than or equal to 365 days:

Group by branchId and year, also sum up the total price. Note: The time field is taken as the first day of the current year.

SPL code:

A1: Query the view through JDBC.

A2: Calculate the time span.

A3: Group and aggregate. When the span is greater than 365 days, the time format mask is yyyy; When the span is less than 30 days, the mask is yyyy-MM-dd, and for other spans, it is set to yyyy-MM. The date function returns the first day of the current year for the yyyy mask time string and the first day of the current month for the yyyy-MM mask time string.

A4: Adjust the field order to meet formatting requirements.

Open source SPL source address

Free Download