mysql5.537安装教程_列存储数据库MonetDB的安装配置

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 = 'KENYA'

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 = 'KENYA'

)

order by

value desc

limit 10;

+------------+-----------------------+

| ps_partkey | value                 |

+============+=======================+

|     195266 |           16538568.54 |

|     113484 |           15794196.80 |

|     120180 |           15785799.99 |

|      82647 |           15716016.89 |

|     127875 |           15634553.80 |

|     107615 |           15145228.84 |

|      19027 |           14424262.29 |

|      44849 |           14306230.64 |

|      35710 |           14123371.56 |

|      95463 |           14113580.11 |

+------------+-----------------------+

10 tuples (96.366ms)

Timer     171.220 msec 10 rows

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 ('AIR', 'TRUCK')

and l_commitdate < l_receiptdate

and l_shipdate < l_commitdate

and l_receiptdate >= date '1995-01-01'

and l_receiptdate < date '1995-01-01' + interval '1' year

group by

l_shipmode

order by

l_shipmode

limit 10;

+------------+-----------------+----------------+

| l_shipmode | high_line_count | low_line_count |

+============+=================+================+

| AIR        |            6261 |           9530 |

| TRUCK      |            6284 |           9444 |

+------------+-----------------+----------------+

2 tuples (1.3s)

Timer    1424.294 msec 2 rows

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 '%special%packages%'

group by

c_custkey

) as c_orders (c_custkey, c_count)

group by

c_count

order by

custdist desc,

c_count desc

limit 10;

+---------+----------+

| c_count | custdist |

+=========+==========+

|       0 |    50004 |

|       9 |     6651 |

|      10 |     6581 |

|      11 |     6030 |

|       8 |     5868 |

|      12 |     5617 |

|      13 |     4966 |

|      19 |     4697 |

|       7 |     4682 |

|      20 |     4568 |

+---------+----------+

10 tuples (649.515ms)

Timer     719.337 msec 10 rows

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-06-01'

and l_shipdate < date '1996-06-01' + interval '1' month

limit 10;

+-----------------------+

| promo_revenue         |

+=======================+

|               16.4120 |

+-----------------------+

1 tuple (378.114ms)

Timer     400.612 msec 1 rows

create view revenue0 (supplier_no, total_revenue) as

select

l_suppkey,

sum(l_extendedprice * (1 - l_discount))

from

lineitem

where

l_shipdate >= date '1995-02-01'

and l_shipdate < date '1995-02-01' + interval '3' month

group by

l_suppkey;

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

limit 10;

drop view revenue0;

+------+--------------+---------------+---------------+-----------------------+

| s_su | s_name       | s_address     | s_phone       | total_revenue         |

: ppke :              :               :               :                       :

: y    :              :               :               :                       :

+======+==============+===============+===============+=======================+

| 5487 | Supplier#000 | nEoE4Yph7gDXN | 27-700-386-25 |          1811427.8649 |

:      : 005487       : GApRdggiY4Ai  : 10            :                       :

+------+--------------+---------------+---------------+-----------------------+

1 tuple (1.2s)

Timer    1388.593 msec 1 rows

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#11'

and p_type not like 'LARGE BRUSHED%'

and p_size in (7, 11, 27, 19, 18, 46, 33, 26)

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 10;

+------------+-------------------------+--------+--------------+

| p_brand    | p_type                  | p_size | supplier_cnt |

+============+=========================+========+==============+

| Brand#22   | LARGE POLISHED BRASS    |     46 |           32 |

| Brand#12   | SMALL POLISHED TIN      |     11 |           24 |

| Brand#24   | SMALL BURNISHED NICKEL  |     11 |           24 |

| Brand#32   | SMALL ANODIZED BRASS    |      7 |           24 |

| Brand#33   | ECONOMY PLATED STEEL    |      7 |           24 |

| Brand#34   | STANDARD ANODIZED BRASS |     33 |           24 |

| Brand#44   | STANDARD PLATED BRASS   |     46 |           24 |

| Brand#52   | MEDIUM ANODIZED STEEL   |     46 |           24 |

| Brand#52   | SMALL PLATED COPPER     |     18 |           24 |

| Brand#54   | PROMO PLATED BRASS      |     27 |           24 |

+------------+-------------------------+--------+--------------+

10 tuples (340.544ms)

Timer     485.096 msec 10 rows

select

sum(l_extendedprice) / 7.0 as avg_yearly

from

lineitem,

part

where

p_partkey = l_partkey

and p_brand = 'Brand#54'

and p_container = 'SM CASE'

and l_quantity < (

select

0.2 * avg(l_quantity)

from

lineitem

where

l_partkey = p_partkey

)

limit 10;

+----------------------+

| avg_yearly           |

+======================+

|           278739.444 |

+----------------------+

1 tuple (4.4s)

Timer    4438.395 msec 1 rows

select

c_name,

c_custkey,

o_orderkey,

o_orderdate,

o_totalprice,

sum(l_quantity)

from

customer,

orders,

lineitem

where

o_orderkey in (

select

l_orderkey

from

lineitem

group by

l_orderkey having

sum(l_quantity) > 315

)

and c_custkey = o_custkey

and o_orderkey = l_orderkey

group by

c_name,

c_custkey,

o_orderkey,

o_orderdate,

o_totalprice

order by

o_totalprice desc,

o_orderdate

limit 100;

+--------------------+---------+-----------+-------------+-------------------+

| c_name             | c_custk | o_orderke | o_orderdate | o_totalprice      |>

:                    : ey      : y         :             :                   :>

+====================+=========+===========+=============+===================+

| Customer#000128120 |  128120 |   4722021 | 1994-04-07  |         544089.09 |

| Customer#000144617 |  144617 |   3043270 | 1997-02-12  |         530604.44 |

| Customer#000066790 |   66790 |   2199712 | 1996-09-30  |         515531.82 |

| Customer#000015619 |   15619 |   3767271 | 1996-08-07  |         480083.96 |

| Customer#000147197 |  147197 |   1263015 | 1997-02-02  |         467149.67 |

| Customer#000117919 |  117919 |   2869152 | 1996-06-20  |         456815.92 |

| Customer#000126865 |  126865 |   4702759 | 1994-11-07  |         447606.65 |

| Customer#000036619 |   36619 |   4806726 | 1995-01-17  |         446704.09 |

| Customer#000119989 |  119989 |   1544643 | 1997-09-20  |         434568.25 |

+--------------------+---------+-----------+-------------+-------------------+

9 tuples (1.6s) !1 column dropped!

Timer    1780.932 msec 9 rows

select

sum(l_extendedprice* (1 - l_discount)) as revenue

from

lineitem,

part

where

(

p_partkey = l_partkey

and p_brand = 'Brand#55'

and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')

and l_quantity >= 4 and l_quantity <= 4 + 10

and p_size between 1 and 5

and l_shipmode in ('AIR', 'AIR REG')

and l_shipinstruct = 'DELIVER IN PERSON'

)

or

(

p_partkey = l_partkey

and p_brand = 'Brand#31'

and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')

and l_quantity >= 12 and l_quantity <= 12 + 10

and p_size between 1 and 10

and l_shipmode in ('AIR', 'AIR REG')

and l_shipinstruct = 'DELIVER IN PERSON'

)

or

(

p_partkey = l_partkey

and p_brand = 'Brand#41'

and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')

and l_quantity >= 22 and l_quantity <= 22 + 10

and p_size between 1 and 15

and l_shipmode in ('AIR', 'AIR REG')

and l_shipinstruct = 'DELIVER IN PERSON'

)

limit 10;

+-----------------------+

| revenue               |

+=======================+

|          4079151.7228 |

+-----------------------+

1 tuple (1.7s)

Timer    1737.761 msec 1 rows

select

s_name,

s_address

from

supplier,

nation

where

s_suppkey in (

select

ps_suppkey

from

partsupp

where

ps_partkey in (

select

p_partkey

from

part

where

p_name like 'sandy%'

)

and ps_availqty > (

select

0.5 * sum(l_quantity)

from

lineitem

where

l_partkey = ps_partkey

and l_suppkey = ps_suppkey

and l_shipdate >= date '1996-01-01'

and l_shipdate < date '1996-01-01' + interval '1' year

)

)

and s_nationkey = n_nationkey

and n_name = 'CHINA'

order by

s_name

limit 10;

+---------------------------+------------------------------------------+

| s_name                    | s_address                                |

+===========================+==========================================+

| Supplier#000000082        | WyTKA7ZpF15t1aCNlT3                      |

| Supplier#000000187        | oMtyTl6hTyLQhiBwrUaa42zKOjfNobEq         |

| Supplier#000000213        | 1s7 4odatD2BWz1QjXR2 2SnFhc,Rvr2Icxh1m7f |

| Supplier#000000319        | tcES8Ay3zcHQER9K6gAKFWlDvcg nrbPl        |

| Supplier#000000345        | ZYuibETx2zArwg                           |

| Supplier#000000388        | n27XQohXrXlJRLdsyXNoljPS                 |

| Supplier#000000411        | G9H53XVrdbhRgvQwho1AS                    |

| Supplier#000000499        | NbcQeBiDiN2tFiVxHIaWU03BVFIuxt           |

| Supplier#000000523        | zzfDhdtZcvmVzA8rNFU,Yctj1zBN             |

| Supplier#000000527        | udKczd6U1Bm79UVDkA8P2Xa1VY qv9mvsXo      |

+---------------------------+------------------------------------------+

10 tuples (790.542ms)

Timer     908.556 msec 10 rows

select

s_name,

count(*) as numwait

from

supplier,

lineitem l1,

orders,

nation

where

s_suppkey = l1.l_suppkey

and o_orderkey = l1.l_orderkey

and o_orderstatus = 'F'

and l1.l_receiptdate > l1.l_commitdate

and exists (

select

*

from

lineitem l2

where

l2.l_orderkey = l1.l_orderkey

and l2.l_suppkey <> l1.l_suppkey

)

and not exists (

select

*

from

lineitem l3

where

l3.l_orderkey = l1.l_orderkey

and l3.l_suppkey <> l1.l_suppkey

and l3.l_receiptdate > l3.l_commitdate

)

and s_nationkey = n_nationkey

and n_name = 'CANADA'

group by

s_name

order by

numwait desc,

s_name

limit 100;

+---------------------------+---------+

| s_name                    | numwait |

+===========================+=========+

| Supplier#000000657        |      20 |

| Supplier#000005855        |      19 |

| Supplier#000009709        |      19 |

| Supplier#000009899        |      12 |

+---------------------------+---------+

100 tuples (3m 23s)

Timer  203544.448 msec 100 rows

select

cntrycode,

count(*) as numcust,

sum(c_acctbal) as totacctbal

from

(

select

substring(c_phone from 1 for 2) as cntrycode,

c_acctbal

from

customer

where

substring(c_phone from 1 for 2) in

('22', '20', '34', '17', '26', '23', '14')

and c_acctbal > (

select

avg(c_acctbal)

from

customer

where

c_acctbal > 0.00

and substring(c_phone from 1 for 2) in

('22', '20', '34', '17', '26', '23', '14')

)

and not exists (

select

*

from

orders

where

o_custkey = c_custkey

)

) as custsale

group by

cntrycode

order by

cntrycode

limit 10;

+-----------------+---------+-------------------+

| cntrycode       | numcust | totacctbal        |

+=================+=========+===================+

| 14              |     952 |        7197284.23 |

| 17              |     860 |        6455570.01 |

| 20              |     913 |        6809667.07 |

| 22              |     893 |        6631741.43 |

| 23              |     892 |        6701457.95 |

| 26              |     859 |        6394689.13 |

| 34              |     947 |        7171947.04 |

+-----------------+---------+-------------------+

7 tuples (271.204ms)

Timer     369.643 msec 7 rows