Напредни извештаи од базата
Средна оценка од сите бизниси кои спаѓаат во категорија по тромесечие
drop view if exists avg_rating_by_category_by_trimesters cascade; create view avg_rating_by_category_by_trimesters as SELECT DISTINCT cc.category_id, cc.category_name, CASE WHEN tabela.quarter=1 THEN tabela.category_avg_stars ELSE 0 END AS first_q, CASE WHEN tabela.quarter=2 THEN tabela.category_avg_stars ELSE 0 END AS second_q, CASE WHEN tabela.quarter=3 THEN tabela.category_avg_stars ELSE 0 END AS third_q, CASE WHEN tabela.quarter=4 THEN tabela.category_avg_stars ELSE 0 END AS fourth_q from business bb join category cc on cc.category_id = bb.category_id join review rr on rr.business_id = bb.business_id join ( select distinct c2.category_id, c2.category_name, extract(quarter FROM r2.review_timestamp) AS quarter, avg(r2.review_stars) as category_avg_stars from business b2 join category c2 on b2.category_id = c2.category_id join review r2 on r2.business_id = b2.business_id group by 1,2,3 ) as tabela on cc.category_id = tabela.category_id
Приказ на бројот на reviews според reviewer по тромесечие
drop view if exists num_of_reviews_by_reviewer_by_trimester cascade; create view num_of_reviews_by_reviewer_by_trimester as SELECT DISTINCT rrr2.reviewer_id, rrr2.reviewer_name, CASE WHEN tabela.quarter=1 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS first_q, CASE WHEN tabela.quarter=2 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS second_q, CASE WHEN tabela.quarter=3 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS third_q, CASE WHEN tabela.quarter=4 THEN tabela.num_of_reviews_by_reviewer ELSE 0 END AS fourth_q from review rrr inner join reviewer rrr2 on rrr.reviewer_id = rrr2.reviewer_id join ( select distinct r2.reviewer_name, r2.reviewer_id, extract(quarter FROM r.review_timestamp) AS quarter, count(r.review_id) as num_of_reviews_by_reviewer from review r inner join reviewer r2 on r.reviewer_id = r2.reviewer_id group by 1,2,3 ) as tabela on rrr2.reviewer_id = tabela.reviewer_id
Last modified
3 years ago
Last modified on 01/25/22 01:11:37
Note:
See TracWiki
for help on using the wiki.