Sorting by specified order

Compute the increase in sales of the first month of a quarter compared to the first month of the previous quarter. There may be no data available for some months.

SQL

WITH M AS
(SELECT 1 month FROM dual UNION SELECT 4 FROM dual UNION
SELECT 7 FROM dual UNION SELECT 10 FROM dual)
SELECT month,sales_amount,
sales_amount-lag(sales_amount) OVER(ORDER BY month)
FROM (SELECT M.month month,
sales_table.sales_amount sales_amount FROM M
left join sales_table on
(M.month=sales_table.month))
ORDER BY month

SQL needs to use outer join to sort rows by the specified order, and also needs to specifically create a sequence number field to ensure the correctness of the order of result set.

SPL

A
1 =demo.query("select * from sales_table").align([1,4,7,10],month)
2 =A1.new(#:month,sales_amount,sales_amount-sales_amount[-1]:increase_amount)

The SPL ad hoc align function can replace the SQL outer join, and its auto-sort record sets eliminate the needs for an extra sequence number field.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.