?求大神解答一道SQL题

catherinefeng 2015-11-15 01:02:52
请大神解答这道题,完全不知道在问什么。附 code。

Please identify what the attached SQL query (query.txt) is gathering and aggregating. Generate a final table with the columns labeled with the original source. In addition, please identify places that could be improved for SQL performance.

select waiters.id as waiter_id,
waiters.first_name as first_name,
waiters.last_name as last_name,
waiters.personal_id as personal_id,
coalesce(payment_table.total_sum,0) as total_payments,
coalesce(tip_table.tip,0) as total_tips,
coalesce(tip_table.percentage,0) as tip_percent,
coalesce(waiter_table.num_tables,0) as num_tables,
coalesce(payment_table.payments,0) as num_payments,
coalesce(items_table.num_items, 0) as num_items,
coalesce(payment_table.rating, 0) as rating,
coalesce(games_table.count, 0) as num_games,
coalesce(tip_table.total_sum,0) as total_tip_payments,
coalesce(emails_table.num_emails,0) as num_emails,
coalesce(payment_table.turn_time,0) as turn_time,
coalesce(features_table.revenue,0) as game_revenue
from waiters
inner join restaurants on restaurants.id = waiters.restaurant_id
left outer join (
select
count(*) as num_tables
from (
select distinct on(date(last_updated), pos_check_number)
*
from piston_check
where last_updated between %s and %s
and waiter_id = %s
order by date(last_updated), pos_check_number, last_updated desc
) as moo
) as waiter_table on true
left outer join(
SELECT
coalesce(sum(total),0) as total_sum,
count(*) as payments,
avg(turn_time) as turn_time,
avg(star_rating) as rating
FROM piston_payment
INNER JOIN piston_check ON piston_payment.check_id = piston_check.id
where piston_payment.last_updated between %s and %s
AND piston_payment.tender_type != 'cash'
and piston_check.waiter_id = %s
) as payment_table on true
left outer join(
select
coalesce(SUM(tip),0) as tip,
coalesce(sum(total),0) as total_sum,
case when (sum(total) - sum(tip) = 0 or sum(tip) = 0) then 0 else sum(tip) / (sum(total) - sum(tip)) *100 end as percentage
from (
SELECT piston_check.waiter_id as waiter_id,
sum(tip) as tip,
sum(total) as total
FROM piston_payment
INNER JOIN piston_check ON piston_payment.check_id = piston_check.id
where piston_payment.last_updated between %s and %s
AND piston_payment.tender_type != 'cash'
and piston_check.waiter_id = %s
group by date(piston_check.last_updated at time zone 'UTC' - interval '11 hours'), piston_check.pos_check_number, waiter_id
) as moo
where case when total-tip=0 or tip=0 then 0 else tip end > 0
) as tip_table on true
left outer join (
select count(*) as num_items
from piston_order_item
inner join piston_order on piston_order_item.order_id = piston_order.id
inner join piston_check on piston_order.check_id = piston_check.id
where piston_order.last_updated between %s and %s
and piston_order_item.item_name != 'Presto Games'
and piston_order_item.parent_order_item_id is null
and piston_check.waiter_id = %s
) as items_table on true
left outer join (
select
count (*) as count
from piston_game
inner join piston_check on piston_check.id = piston_game.check_id
where piston_game.last_updated between %s and %s
and piston_check.waiter_id = %s
) as games_table on true
left outer join(
select count(*) as num_emails
from piston_email
inner join piston_check on check_id = piston_check.id
where piston_email.last_updated between %s and %s
and piston_check.waiter_id = %s
and email ~* '^[A-Za-z0-9._%%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'
and not email ~* 'elacarte.com$'
and joined_club=true
) as emails_table on true
left outer join (
select sum(charge) as revenue
from piston_game_feature
inner join piston_game on game_id = piston_game.id
inner join piston_check on piston_check.id = piston_game.check_id
where piston_game.last_updated between %s and %s
and piston_check.waiter_id = %s
) as features_table on true
where restaurants.code = %s
and waiters.id = %s

...全文
22 点赞 收藏 回复
写回复
回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-11-15 01:02
社区公告
暂无公告