查询场景

select  
    l_orderkey,  
    sum(l_extendedprice*(1-l_discount)) as revenue,  //聚合：潜在的收入 
    o_orderdate,  
    o_shippriority  
from  
    customer, orders, lineitem
where  
    c_mktsegment = 'MACHINERY'     // 谓词过滤
    and c_custkey = o_custkey  
    and l_orderkey = o_orderkey  
    and o_orderdate < date '1995-03-23'
    and o_orderdate > date '1995-02-23' // 谓词过滤,指定时间
    and l_shipdate > date '1995-03-23'  
    and l_shipdate < date '1996-03-23'
group by //分组操作  
    l_orderkey, //订单标识  
    o_orderdate, //订单日期  
    o_shippriority //运输优先级  
order by 
    revenue desc, //TopN：降序排序，把潜在最大收入列在前面  
    o_orderdate;
limit 
    100;

测试结果如下：

select
        n_name,
        sum(l_extendedprice * (1 - l_discount)) as revenue  //聚合：潜在的收入 
from
        customer,
        orders,
        lineitem,
        supplier,
        nation,
        region   //六表连接  
where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and l_suppkey = s_suppkey
        and c_nationkey = s_nationkey
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'EUROPE'  // 谓词过滤：指定地区
        and o_orderdate >= date '1996-01-01'  //谓词过滤：指定时间
        and o_orderdate < date '1996-01-01' + interval '1' year
        and l_shipdate > date '1996-02-23'  
          and l_shipdate < date '1996-03-23'
group by
        n_name
order by   // TopN：按收入降序排序，注意分组和排序子句不同  
        revenue desc
limit 10

测试结果如下：