Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
-- dbext:type=PGSQL:user=yangchenyun:dbname=huali_development:host=localhost:port=5432
select source, count(id)
from orders
group by source
;
select name_zh, sum(quantity)
from products, line_items
where products.id = line_items.product_id
group by name_zh
;
select
-- order information
orders.id, orders.delivery_date,
-- shipment information
addresses.fullname,
addresses.phone,
areas.name,
addresses.address,
orders.special_instructions
from orders, shipments, ship_methods, addresses, areas
where orders.id = shipments.order_id
and shipments.ship_method_id = ship_methods.id
and ship_methods.id = 3
and orders.state = 'wait_ship'
and orders.delivery_date = '2013-02-14'
and addresses.id = orders.address_id
and areas.id = addresses.area_id
order by areas.name
;
-- Operation Data
-- Monthly Aggregation
select date_part('month', orders.created_at) as Month,
count(distinct orders.id) as OrderNumber,
sum(payment_total) as Revenue
from orders
where (state != 'void' and state != 'generated')
GROUP BY date_part('month', orders.created_at)
;
select count(id)
from orders
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and (state != 'void' and state != 'generated')
;
-- Daily Details
select date_part('day', orders.created_at) as day,
payment_total as paid_amount,
orders.created_at as purchase_time,
products.name_zh
from orders, line_items, products
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and orders.id = line_items.order_id
and line_items.product_id = products.id
and (state != 'void' and state != 'generated')
order by day
;
-- Daily Distribution
select date_part('day', orders.created_at) as day, count(id) as orderNum
from orders
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and (state != 'void' and state != 'generated')
group by date_part('day', orders.created_at)
order by day
;
-- Order Analysis
-- by Area
select date_part('month', orders.created_at) as Month,
provinces.name,
count(orders.id),
sum(payment_total) as Revenue,
round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders, addresses, provinces
where orders.address_id = addresses.id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and provinces.id = addresses.province_id
and (state != 'void' and state != 'generated')
group by provinces.name, date_part('month', orders.created_at)
order by month, count(orders.identifier) desc
;
-- by Source
select date_part('month', orders.created_at) as Month,
source,
count(orders.id),
sum(payment_total) as Revenue,
round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders
where (state != 'void' and state != 'generated')
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
group by orders.source, date_part('month', orders.created_at)
order by month, count(orders.identifier) desc
;
-- by Product
select date_part('month', orders.created_at) as Month,
products.name_zh,
count(orders.id),
-- payment_total is calculated multiple times for orders which have multiple products
sum(payment_total) as Revenue
from orders, line_items, products
where orders.id = line_items.order_id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and line_items.product_id = products.id
and (state != 'void' and state != 'generated')
group by products.id, date_part('month', orders.created_at)
order by month, count(orders.identifier) desc
;
select *
from line_items as l1, line_items as l2
where l1.order_id = l2.order_id
and l1.id <> l2.id
;
-- by Delivery
select date_part('month', orders.created_at) as Month,
ship_methods.name,
count(orders.id) as OrderNum,
sum(payment_total) as Revenue,
round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders, shipments, ship_methods
where orders.id = shipments.order_id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and shipments.ship_method_id = ship_methods.id
and (orders.state != 'void' and orders.state != 'generated')
group by ship_methods.name, date_part('month', orders.created_at)
order by month, count(orders.id) desc
;
select date_part('month', orders.created_at) as Month,
ship_methods.name,
count(orders.id) as OrderNum,
sum(payment_total) as Revenue,
round(sum(payment_total)/ count(orders.id), 2) as RevenuePerOrder
from orders, shipments, ship_methods
where orders.id = shipments.order_id
and orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and (orders.state != 'void' and orders.state != 'generated')
and shipments.ship_method_id = ship_methods.id
group by ship_methods.name, date_part('month', orders.created_at)
order by month, count(orders.id) desc
;
select * from orders
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and (orders.state != 'void' and orders.state != 'generated')
EXCEPT
select orders.*
from orders, shipments
where orders.created_at > '2013-02-01' and orders.created_at < '2013-03-01'
and orders.id = shipments.order_id
and (orders.state != 'void' and orders.state != 'generated')
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.