Let's look at the following example:
Say, an imaginary company and it's database schema is same as Northwind Database.
It is a simple order processing database.
At the end of the sales year, they want a following sales report.
Get top 10 sales amount
and the employees who made that sales.
To design the query, I have to access 3 tables and they are: Order Details, Orders, and Employees.
Designing query in PipeSQL
--step 1:
od := [Order Details]
summary sum(
od.UnitPrice * od.Quantity * (1 - od.Discount)
) by OrderID
|>
ord := Orders
filter ord.OrderID = od.OrderID
column ord.OrderID, ord.ShippedDate, ord.EmployeeID,
od.summary1 SaleAmount
|>
emp := Employees
filter emp.EmployeeID = ord.EmployeeID
column
emp.Country,
emp.LastName,
emp.FirstName,
ord.OrderID,
ord.ShippedDate,
ord.SaleAmount
sort ord.SaleAmount -
top 10;
You can run this query on a live database from here: play
Let's look at the pipeSQL explanation:
Step 1: Fetch sum of sales for each orders
Summarize the [Order Details] table rows by grouping on OrderID and calculate the total sales amount for each orders.
Pipe (|>)
the result to the next step.
Step 2: Fetch EmployeeID for each orders
This step is going to receive the step 1 result as an input dataset. We have to specify the filter rule on how
to process this input dataset.
Filter rule,
filter ord.OrderID = od.OrderID
, is actually a SQL JOIN condition (or SQL EXISTS condition) and the begins with
keyword
filter
.
Under this step, [Order Details] and Orders table are joined based on the filter condition.
Keyword
column
specifies the columns, for example EmployeeID, which needs to be included in the result.
Pipe
(|>)
the result to the next step.
Step 3: Fetch top 10 max sales employees
This step is going to receive the step 2 result as an input dataset and the filter rule is
filter emp.EmployeeID = ord.EmployeeID
.
This steps joins Orders and Employees table based on the filter condition. Since we need the max sales amount, we have
to sort it in descending order. To do that, we use sort statement which is noted by keyword
sort
.
Here
sort ord.SaleAmount -
will sort the results on descending and
top 10
will only fetch the first 10 rows.
And the pipe chains ends with a
;
.
Finally, pipeSQL query will be generated to appropriate SQL dialect, for this example, into Oracle SQL.
select *
from (
select
emp.Country,
emp.LastName,
emp.FirstName,
ord.OrderID,
ord.ShippedDate,
ord.SaleAmount
from Employees emp
join (
select
ord.OrderID,
ord.ShippedDate,
ord.EmployeeID,
od.summary1 SaleAmount
from Orders ord
join (
select
OrderID,
sum(
od.UnitPrice * od.Quantity * (1 - od.Discount)
) summary1
from "Order Details" od
group by
OrderID
) od on
(
ord.OrderID = od.OrderID
)
) ord on
(
emp.EmployeeID = ord.EmployeeID
)
order by ord.SaleAmount desc
) toplist
where rownum <= 10
For all available features of PipeSQL, please check the documentation