456 lines
8.0 KiB
TeX
456 lines
8.0 KiB
TeX
%
|
|
%
|
|
% TPC-D Queries
|
|
%
|
|
%
|
|
|
|
\chapter{Geschachtelte TPC-D Abfragen}
|
|
|
|
\section*{Q2}
|
|
\label{sec:Q2}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[sacctbal, sname,
|
|
nname, ppartkey,
|
|
pmfgr, saddress,
|
|
sphone, scomment]
|
|
from
|
|
[part, supplier,
|
|
partsupp, nation, region]
|
|
where
|
|
[ppartkey = pspartkey,
|
|
ssuppkey = pssuppkey,
|
|
psize = 15,
|
|
ptype contains "BRASS",
|
|
snationkey = nnationkey,
|
|
nregionkey = rregionkey,
|
|
rname = "EUROPE",
|
|
pssupplycost = (
|
|
select
|
|
min(ps:pssupplycost)
|
|
from
|
|
[partsupp as ps, supplier as s,
|
|
nation as n, region as r]
|
|
where
|
|
[ppartkey = ps:pspartkey,
|
|
s:ssuppkey = ps:pssuppkey,
|
|
s:snationkey = n:nnationkey,
|
|
n:nregionkey = r:rregionkey,
|
|
r:rname = "EUROPE"]
|
|
)]
|
|
orderby[sacctbal desc,
|
|
nname,
|
|
sname,
|
|
ppartkey]
|
|
first 100
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q4}
|
|
\label{sec:Q4}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[oorderpriority,
|
|
count(*) as ordercount]
|
|
from orders
|
|
where
|
|
[oorderdate >= instant("1993-07-01"),
|
|
oorderdate < theInstant(
|
|
year_of(instant("1993-07-01")),
|
|
month_of(instant("1993-07-01")) + 3,
|
|
day_of(instant("1993-07-01"))
|
|
),
|
|
exists(
|
|
select *
|
|
from lineitem
|
|
where
|
|
[lorderkey = oorderkey, lcommitdate < lreceiptdate]
|
|
)]
|
|
groupby [oorderpriority]
|
|
orderby [oorderpriority]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q7}
|
|
\label{sec:Q7}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[supp_nation,
|
|
cust_nation,
|
|
lyear, sum(volume) as revenue]
|
|
from (
|
|
select
|
|
[n1:nname as supp_nation,
|
|
n2:nname as cust_nation,
|
|
year_of(lshipdate) as lyear,
|
|
lextendedprice * (1 - ldiscount) as volume]
|
|
from
|
|
[supplier, lineitem,
|
|
orders, customer,
|
|
nation as n1, nation as n2]
|
|
where
|
|
[ssuppkey = lsuppkey,
|
|
oorderkey = lorderkey,
|
|
ccustkey = ocustkey,
|
|
snationkey = n1:nnationkey,
|
|
cnationkey = n2:nnationkey,
|
|
(n1:nname = "FRANCE" and n2:nname = "GERMANY")
|
|
or
|
|
(n1:nname = "GERMANY" and n2:nname = "FRANCE"),
|
|
between(instant2real(lshipdate),
|
|
instant2real(instant("1995-01-01")),
|
|
instant2real(instant("1996-12-31"))
|
|
)]
|
|
) as shipping
|
|
groupby [supp_nation, cust_nation, lyear]
|
|
orderby [supp_nation, cust_nation, lyear]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q8}
|
|
\label{sec:Q8}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[oyear,
|
|
(aggregate((ifthenelse(nation = "BRAZIL", volume, 0.0)), (+), 'real', '[const real value 0.0]')
|
|
/ sum(volume)) as mktshare]
|
|
from (
|
|
select
|
|
[year_of(oorderdate) as oyear,
|
|
lextendedprice * (1-ldiscount) as volume,
|
|
n2:nname as nation]
|
|
from
|
|
[part, supplier,
|
|
lineitem, orders,
|
|
customer, nation as n1,
|
|
nation as n2, region]
|
|
where
|
|
[ppartkey = lpartkey,
|
|
ssuppkey = lsuppkey,
|
|
lorderkey = oorderkey,
|
|
ocustkey = ccustkey,
|
|
cnationkey = n1:nnationkey,
|
|
n1:nregionkey = rregionkey,
|
|
rname = "AMERICA",
|
|
snationkey = n2:nnationkey,
|
|
between(instant2real(oorderdate),
|
|
instant2real(instant("1995-01-01")),
|
|
instant2real(instant("1996-12-31"))),
|
|
ptype = "ECONOMY ANODIZED STEEL"]
|
|
) as allnations
|
|
groupby [oyear]
|
|
orderby [oyear]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q9}
|
|
\label{sec:Q9}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[nation,
|
|
oyear,
|
|
sum(amount) as sumprofit]
|
|
from (
|
|
select
|
|
[nname as nation,
|
|
year_of(oorderdate) as oyear,
|
|
lextendedprice * (1 - ldiscount) - pssupplycost * lquantity as amount]
|
|
from
|
|
[part, supplier,
|
|
lineitem, partsupp,
|
|
orders, nation]
|
|
where
|
|
[ssuppkey = lsuppkey,
|
|
pssuppkey = lsuppkey,
|
|
pspartkey = lpartkey,
|
|
ppartkey = lpartkey,
|
|
oorderkey = lorderkey,
|
|
snationkey = nnationkey,
|
|
pname contains "green"]
|
|
) as profit
|
|
groupby
|
|
[nation, oyear]
|
|
orderby
|
|
[nation, oyear desc]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q11}
|
|
\label{sec:Q11}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[pspartkey, (sum(pssupplycost * psavailqty)) as value]
|
|
from
|
|
[partsupp, supplier, nation]
|
|
where
|
|
[pssuppkey = ssuppkey,
|
|
snationkey = nnationkey,
|
|
nname = "GERMANY"]
|
|
groupby [pspartkey]
|
|
having
|
|
[sum(pssupplycost * psavailqty) > (
|
|
select
|
|
[sum(pssupplycost * psavailqty * 0.0001)]
|
|
from
|
|
[partsupp, supplier, nation]
|
|
where
|
|
[pssuppkey = ssuppkey,
|
|
snationkey = nnationkey,
|
|
nname = "GERMANY"]
|
|
)]
|
|
orderby [value desc]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q13}
|
|
\label{sec:Q13}
|
|
|
|
\begin{lstlisting}
|
|
select [ccount, count(*) as custdist]
|
|
from (
|
|
select
|
|
[ccustkey, count(oorderkey) as ccount]
|
|
from
|
|
customer
|
|
left outer join
|
|
orders
|
|
on
|
|
[ccustkey = ocustkey,
|
|
not(ocomment contains "special"),
|
|
not(ocomment contains "request")]
|
|
groupby [ccustkey]
|
|
)as corders
|
|
groupby [ccount]
|
|
orderby [custdist desc, ccount desc]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q15}
|
|
\label{sec:Q15}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[ssuppkey, sname,
|
|
saddress, sphone,
|
|
total_revenue]
|
|
from
|
|
[supplier, revenue]
|
|
where
|
|
[ssuppkey = supplier_no,
|
|
total_revenue = (select max(total_revenue) from revenue)]
|
|
orderby [ssuppkey]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q16}
|
|
\label{sec:Q16}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[pbrand, ptype, psize,
|
|
count(distinct pssuppkey) as suppliercnt]
|
|
from
|
|
[partsupp, part]
|
|
where
|
|
[ppartkey = pspartkey,
|
|
not(pbrand = "Brand#45"),
|
|
not(ptype starts "MEDIUM POLISHED"),
|
|
psize in (49, 14, 23, 45, 19, 3, 36, 9),
|
|
pssuppkey not in(
|
|
select ssuppkey
|
|
from supplier
|
|
where
|
|
[scomment contains "Customer",
|
|
scomment contains "Complaints"]
|
|
)]
|
|
groupby
|
|
[pbrand, ptype, psize]
|
|
orderby
|
|
[suppliercnt desc, pbrand, ptype, psize]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q17}
|
|
\label{sec:Q17}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[sum(lextendedprice / 7.0) as avg_yearly]
|
|
from
|
|
[lineitem, part]
|
|
where
|
|
[ppartkey = lpartkey,
|
|
pbrand = "Brand#23",
|
|
pcontainer = "MED BOX",
|
|
lquantity < (
|
|
select
|
|
avg(0.2 * l1:lquantity)
|
|
from
|
|
[lineitem as l1]
|
|
where
|
|
[l1:lpartkey = ppartkey]
|
|
)]
|
|
groupby []
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q18}
|
|
\label{sec:Q18}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[cname, ccustkey,
|
|
oorderkey, oorderdate,
|
|
ototalprice, sum(lquantity) as quant]
|
|
from
|
|
[customer, orders, lineitem]
|
|
where
|
|
[oorderkey in (
|
|
select lorderkey
|
|
from lineitem
|
|
groupby [lorderkey]
|
|
having [sum(lquantity) > 300]
|
|
),
|
|
ccustkey = ocustkey,
|
|
oorderkey = lorderkey]
|
|
groupby
|
|
[cname, ccustkey,
|
|
oorderkey, oorderdate,
|
|
ototalprice]
|
|
orderby [ototalprice desc, oorderdate]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q20}
|
|
\label{sec:Q20}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[sname,
|
|
saddress]
|
|
from
|
|
[supplier, nation]
|
|
where
|
|
[ssuppkey in (
|
|
select
|
|
[pssuppkey]
|
|
from
|
|
[partsupp]
|
|
where
|
|
[pspartkey in (
|
|
select ppartkey
|
|
from part
|
|
where
|
|
tostring(pname) starts "forest"
|
|
),
|
|
psavailqty > (
|
|
select sum(lquantity * 0.5)
|
|
from lineitem
|
|
where
|
|
[lpartkey = pspartkey,
|
|
lsuppkey = pssuppkey,
|
|
lshipdate >= instant("1994-01-01"),
|
|
lshipdate < theInstant(
|
|
year_of(instant("1994-01-01")) + 1,
|
|
month_of(instant("1994-01-01")),
|
|
day_of(instant("1994-01-01"))
|
|
)]
|
|
)]
|
|
),
|
|
snationkey = nnationkey,
|
|
nname = "CANADA"]
|
|
orderby [sname]
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q21}
|
|
\label{sec:Q21}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[sname, count(*) as numwait]
|
|
from
|
|
[supplier, lineitem as l1,
|
|
orders, nation]
|
|
where
|
|
[ssuppkey = l1:lsuppkey,
|
|
oorderkey = l1:lorderkey,
|
|
oorderstatus = "F",
|
|
l1:lreceiptdate > l1:lcommitdate,
|
|
exists(
|
|
select *
|
|
from lineitem as l2
|
|
where
|
|
[l2:lorderkey = l1:lorderkey,
|
|
not(l2:lsuppkey = l1:lsuppkey)]
|
|
),
|
|
not(exists(
|
|
select *
|
|
from lineitem as l3
|
|
where
|
|
[l3:lorderkey = l1:lorderkey,
|
|
not(l3:lsuppkey = l1:lsuppkey),
|
|
l3:lreceiptdate > l3:lcommitdate]
|
|
)),
|
|
snationkey = nnationkey,
|
|
nname = "SAUDI ARABIA"]
|
|
groupby [sname]
|
|
orderby [numwait desc, sname]
|
|
first 100
|
|
\end{lstlisting}
|
|
|
|
\clearpage
|
|
|
|
\section*{Q22}
|
|
\label{sec:Q22}
|
|
|
|
\begin{lstlisting}
|
|
select
|
|
[cntrycode,
|
|
count(*) as numcust,
|
|
sum(cacctbal) as totacctbal]
|
|
from (
|
|
select
|
|
[substr(cphone, 1, 2) as cntrycode, cacctbal]
|
|
from customer
|
|
where
|
|
[substr(cphone, 1, 2) in ("13","35","31","23","29","30","18"),
|
|
cacctbal > (
|
|
select avg(c1:cacctbal)
|
|
from customer as c1
|
|
where
|
|
[c1:cacctbal > 0.00,
|
|
substr(c1:cphone, 1, 2) in ("13","35","31","23","29","30","18")]
|
|
),
|
|
not( exists(
|
|
select *
|
|
from orders
|
|
where ocustkey = ccustkey
|
|
))]
|
|
) as custsale
|
|
groupby [cntrycode]
|
|
orderby [cntrycode]
|
|
\end{lstlisting}
|
|
|
|
%
|
|
% EOF
|
|
%
|
|
% |