|
|
莫名的17,同时难倒了dm和pg，oracle 单进程只要17秒
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q7.txt
isql V6.0.2.77-Build(2010.12.23)
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
(
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA')
or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
supp_nation cust_nation l_year revenue
1 ARGENTINA FRANCE 1995 531156727.2505
2 ARGENTINA FRANCE 1996 538746468.5274
3 FRANCE ARGENTINA 1995 523667981.8327
4 FRANCE ARGENTINA 1996 529761492.9053
4 rows got
time used: 654586.891(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q8.txt
isql V6.0.2.77-Build(2010.12.23)
select
o_year,
sum(case
when nation = 'ARGENTINA' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'ECONOMY BURNISHED TIN'
) as all_nations
group by
o_year
order by
o_year;
o_year mkt_share
1 1995 .039554
2 1996 .038464
2 rows got
time used: 132564.261(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/9.txt
isql V6.0.2.77-Build(2010.12.23)
fail to open include file
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/9.txt
isql V6.0.2.77-Build(2010.12.23)
fail to open include file
[root@redflag11012501 bin]#
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q10.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q10.txt'文件第 20 行: 'and'附近有语法错误
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q11.txt
isql V6.0.2.77-Build(2010.12.23)
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'JAPAN'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0001000000
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'JAPAN'
)
order by
value desc;
ps_partkey value
0 rows got
time used: 34379.526(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q12.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q12.txt'文件第 25 行: 'group'附近有语法错误
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q13.txt
isql V6.0.2.77-Build(2010.12.23)
select
c_count,
count(*) as custdist
from
(
select
c_custkey,
count(o_orderkey)
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%pending%accounts%'
group by
c_custkey
) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc;
c_count custdist
1 0 500018
2 10 66108
3 9 65100
45 46 1
46 44 1
46 rows got
time used: 505786.667(ms).
[root@redflag11012501 bin]# vi ./sql/q12.txt
end) as low_line_count
lineitem
and l_receiptdate < date '1996-01-01' + interval '1 year'
l_shipmode
l_shipmode;
~
~
~
~
~
"./sql/q12.txt" 28L, 567C 已写入
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q12.txt
isql V6.0.2.77-Build(2010.12.23)
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('FOB', 'TRUCK')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1996-01-01'
and l_receiptdate < date '1996-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode;
l_shipmode high_line_count low_line_count
1 FOB 62623 93407
2 TRUCK 62305 93899
2 rows got
time used: 127980.990(ms).
[root@redflag11012501 bin]# vi ./sql/q4.txt
count(*) as order_count
orders
)
o_orderpriority
o_orderpriority;
~
~
~
~
~
~
~
~
"./sql/q4.txt" 21L, 335C 已写入
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q4.txt
isql V6.0.2.77-Build(2010.12.23)
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1997-07-01'
and o_orderdate < date '1997-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
o_orderpriority order_count
1 1-URGENT 105830
2 2-HIGH 105465
3 3-MEDIUM 104928
4 4-NOT SPECIFIED 104715
5 5-LOW 104977
5 rows got
time used: 139931.890(ms).
[root@redflag11012501 bin]# vi ./sql/q10.txt
c_comment
nation
and c_nationkey = n_nationkey
c_comment
limit 20;
~
~
~
~
~
~
~
"./sql/q10.txt" 32L, 506C 已写入
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q10.txt
isql V6.0.2.77-Build(2010.12.23)
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1993-03-01'
and o_orderdate < date '1993-03-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20;
c_custkey c_name revenue c_acctbal n_name c_address c_ph
one c_comment
1 29581 Customer#000029581 783023.0226 -629.61 ARGENTINA t2GMv bgtIVPb4towKYQKBs6EGh,
1s7BRf 11-133-736-8171 . quickly final accounts wake since the even instructions. regular, permanent accounts are against the final
p
2 76033 Customer#000076033 777093.4350 -718.33 ALGERIA TqJsSBmjyoU0f8oXBMuqB9CQ7XB6
XfZOpGL 10-645-182-3231 ay after the excuses. idly even accounts cajole slyly even dependencies; bold requests along the fo
20 254452 Customer#000254452 689498.7780 7459.78 ROMANIA hcLk7NcxWq 29-487-552-4
072 ggle; carefully bold deposits boost blithely among th
20 rows got
time used: 98299.263(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q14.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q14.txt'文件第 13 行: ';'附近有语法错误
[root@redflag11012501 bin]# vi ./sql/q14.txt
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
part
and l_shipdate < date '1996-04-01' + interval '1 month';
~
~
~
~
~
~
~
"./sql/q14.txt" 13L, 317C 已写入
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q14.txt
isql V6.0.2.77-Build(2010.12.23)
select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1996-04-01'
and l_shipdate < date '1996-04-01' + interval '1' month;
promo_revenue
1 16.646592
1 rows got
time used: 105748.484(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q15.txt
isql V6.0.2.77-Build(2010.12.23)
'./sql/q15.txt'文件第 10 行: 'group'附近有语法错误
[root@redflag11012501 bin]# vi ./sql/q15.txt
l_suppkey;
total_revenue
revenue0
)
s_suppkey;
drop view revenue0;
~
~
~
~
~
~
~
"./sql/q15.txt" 34L, 497C 已写入
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q15.txt
isql V6.0.2.77-Build(2010.12.23)
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '1995-12-01'
and l_shipdate < date '1995-12-01' + interval '3' month
group by
l_suppkey;
time used: 41.727(ms).
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey;
s_suppkey s_name s_address s_phone total_revenue
1 33987 Supplier#000033987 oHFS5orEHtYMgOS3RGWR 13-117-218-7219 2296670.4443
1 rows got
time used: 202827.374(ms).
drop view revenue0;
time used: 59.071(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q16.txt
isql V6.0.2.77-Build(2010.12.23)
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#35'
and p_type not like 'ECONOMY BURNISHED%'
and p_size in (14, 7, 21, 24, 35, 33, 2, 20)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
limit 100;
p_brand p_type p_size supplier_cnt
1 Brand#12 STANDARD POLISHED COPPER 14 100
2 Brand#33 LARGE BURNISHED NICKEL 35 100
3 Brand#54 PROMO ANODIZED BRASS 35 100
100 Brand#11 STANDARD PLATED TIN 24 80
100 rows got
time used: 29390.567(ms).
[root@redflag11012501 bin]# ./isql localhost sysdba sysdba 12345 ./sql/q17.txt
isql V6.0.2.77-Build(2010.12.23) |
|
楼主: 〇〇
IP卡
狗仔卡
楼主
显身卡
发表于 2011-12-11 13:32