Database Reference
In-Depth Information
Both types of queries can be either written manually by programmers or data analysts,
or they can emerge as a result of a query generator.
Getting ready
First, verify that you really do have a complex query.
A query which simply returns lots of database field is not complex by itself. In order to be
complex, the query has to join lots of tables in complex ways.
The easiest way to find out if the query itself is complex is to look at the output of
EXPLAIN
.
If it has lots of rows, the query is complex, not just having lot of text.
How to do it...
Simplifying a query usually means restructuring it, so that parts of it can be defined separately
and then used by other parts.
We illustrate the possibilities with rewriting the following query in several ways (full code is in
ile
shop_database.tar.gz
). It is a so-called "pivot" or "cross-tab" query, getting quarterly
profit for non-local sales from all shops, as shown next:
SELECT shop.sp_name AS shop_name,
q1_nloc_profit.profit as q1_profit,
q2_nloc_profit.profit as q2_profit,
q3_nloc_profit.profit as q3_profit,
q4_nloc_profit.profit as q4_profit,
year_nloc_profit.profit as year_profit
FROM (SELECT * FROM salespoint ORDER BY sp_name) AS shop
LEFT JOIN (
SELECT
spoint_id,
sum(sale_price) - sum(cost) AS profit,
count(*) AS nr_of_sales
FROM sale s
JOIN item_in_wh iw ON s.item_in_wh_id=iw.id
JOIN item i ON iw.item_id = i.id
JOIN salespoint sp ON s.spoint_id = sp.id
JOIN location sploc ON sp.loc_id = sploc.id
JOIN warehouse wh ON iw.whouse_id = wh.id
JOIN location whloc ON wh.loc_id = whloc.id
WHERE sale_time >= '2009-01-01'
AND sale_time < '2009-04-01'
AND sploc.id != whloc.id
GROUP BY 1
