AnalyticDB for MySQL:Query scenarios

select  
    l_orderkey,  
    sum(l_extendedprice*(1-l_discount)) as revenue,  // Aggregate the potential revenues. 
    o_orderdate,  
    o_shippriority  
from  
    customer, orders, lineitem
where  
    c_mktsegment = 'MACHINERY'  // Perform predicate filtering.
    and c_custkey = o_custkey  
    and l_orderkey = o_orderkey  
    and o_orderdate < date '1995-03-23'
    and o_orderdate > date '1995-02-23'  // Specify the date range over which you want to query orders.
    and l_shipdate > date '1995-03-23'  
    and l_shipdate < date '1996-03-23'
group by // Perform grouping operations.  
    l_orderkey, // Specify the order identifier.  
    o_orderdate, // Specify the order date.  
    o_shippriority // Specify the delivery priority.  
order by 
    revenue desc, // Sort the orders in descending order of potential revenue.  
    o_orderdate;
limit 
    100;

The following table lists the test results.

select
        n_name,
        sum(l_extendedprice * (1 - l_discount)) as revenue  // Aggregate the potential revenues. 
from
        customer,
        orders,
        lineitem,
        supplier,
        nation,
        region  // Associate the six tables.  
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'  // Specify the region of the suppliers.
        and o_orderdate >= date '1996-01-01'  // Specify the date range over which you want to query orders.
        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  // Sort the suppliers in descending order of revenue. Take note of the difference between the GROUP BY and ORDER BY clauses.  
        revenue desc
limit 10

The following table lists the test results.