33 lines
1.7 KiB
Plaintext
33 lines
1.7 KiB
Plaintext
# Hand optimized query plan for TPC-1
|
|
#
|
|
|
|
#debug 3;
|
|
|
|
query
|
|
LINEITEM_25
|
|
loop[. feed
|
|
project [ L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX ]
|
|
filter[ .L_SHIPDATE < cmpdate1 ]
|
|
sortby[L_RETURNFLAG asc, L_LINESTATUS asc]
|
|
groupby[L_RETURNFLAG, L_LINESTATUS; Count_order: group feed count ,
|
|
Sum_qty: group feed sum[L_QUANTITY] ,
|
|
Sum_base_price: group feed sum[L_EXTENDEDPRICE] ,
|
|
Sum_disc_price: group feed
|
|
extend[xxxExprField: (.L_EXTENDEDPRICE * (1 - .L_DISCOUNT))] sum[xxxExprField] ,
|
|
Sum_charge: group feed
|
|
extend[xxxExprField: ((.L_EXTENDEDPRICE * (1 - .L_DISCOUNT)) * (1 + .L_TAX))] sum[xxxExprField] ,
|
|
Avg_qty: group feed avg[L_QUANTITY] ,
|
|
Avg_price: group feed avg[L_EXTENDEDPRICE] ,
|
|
Avg_disc: group feed avg[L_DISCOUNT] ] consume]
|
|
summarize {G}
|
|
sortby[L_RETURNFLAG_G asc, L_LINESTATUS_G asc]
|
|
groupby[L_RETURNFLAG_G, L_LINESTATUS_G; Count_order: group feed sum[Count_order_G] ,
|
|
Sum_qty: group feed sum[Sum_qty_G] ,
|
|
Sum_base_price: group feed sum[Sum_base_price_G] ,
|
|
Sum_disc_price: group feed sum[Sum_disc_price_G] ,
|
|
Sum_charge: group feed sum[Sum_charge_G] ,
|
|
Avg_qty: group feed avg[Avg_qty_G] ,
|
|
Avg_price: group feed avg[Avg_price_G] ,
|
|
Avg_disc: group feed avg[Avg_disc_G] ]
|
|
consume;
|