DatabaseCreation: views_.sql

File views_.sql, 8.3 KB (added by 231072, 2 days ago)
Line 
1-- преглед на сите активни продукти
2CREATE OR REPLACE VIEW vw_active_products AS
3SELECT
4 p.product_id,
5 p.title,
6 p.price,
7 p.currency,
8
9 CASE
10 WHEN p.quantity <= 5 THEN 1
11 ELSE 0
12 END AS low_stock,
13
14 ( SELECT pi.image_url
15 FROM productimages pi
16 WHERE pi.product_id = p.product_id
17 ORDER BY pi.image_id
18 LIMIT 1) AS thumbnail,
19
20
21 u.user_id AS seller_id,
22 c.category_id,
23 p.location
24
25
26FROM product p
27JOIN category c ON c.category_id = p.category_id
28JOIN appuser u ON u.user_id = p.seller_id
29WHERE p.is_active = 1 AND p.quantity > 0 AND u.is_active = 1;
30
31
32-------------------------------------------------------
33-- детален преглед на избран продукт
34
35CREATE OR REPLACE VIEW vw_product_details AS
36
37WITH product_rating_reviews AS (
38 SELECT r.product_id,
39 avg(r.rating) AS average_rating,
40 count(r.review_id) AS total_reviews
41 FROM review r
42 GROUP BY r.product_id
43 ),
44
45 product_images AS (
46 SELECT pi.product_id,
47 array_agg(pi.image_url ORDER BY pi.image_id) AS images,
48 min(pi.image_url) AS thumbnail
49 FROM productimages pi
50 GROUP BY pi.product_id
51 )
52
53 SELECT
54 p.product_id,
55 p.title,
56 p.description,
57 p.price,
58 p.currency,
59 p.created_at,
60 p.location,
61 CASE
62 WHEN p.quantity <= 5 THEN 1
63 ELSE 0
64 END AS low_stock,
65
66 c.name,
67
68 u.username AS seller_username,
69 u.first_name,
70 u.last_name,
71
72
73 ps.average_rating,
74 ps.total_reviews,
75 i.images,
76 i.thumbnail
77
78FROM product p
79JOIN category c ON c.category_id = p.category_id
80JOIN appuser u ON u.user_id = p.seller_id
81LEFT JOIN product_rating_reviews ps ON ps.product_id = p.product_id
82LEFT JOIN product_images i ON i.product_id = p.product_id;
83
84
85-----------------------------------------------------
86-- преглед на reviews за конкретен продукт
87DROP VIEW vw_product_reviews
88
89CREATE OR REPLACE VIEW vw_product_reviews AS
90SELECT
91 r.review_id,
92 r.product_id,
93 p.title,
94 r.rating,
95 r.comment,
96 r.created_at,
97
98 r.buyer_id,
99 ub.username AS buyer_username,
100 r.seller_id
101
102FROM review r
103JOIN product p ON p.product_id = r.product_id
104JOIN appuser ub ON ub.user_id = r.buyer_id;
105
106
107-------------------------------------------------------------
108--------------------------------------------------------------
109-- преглед на листата со омилени продукти на корисникот.
110CREATE OR REPLACE VIEW vw_favorites AS
111SELECT
112 f.user_id,
113
114 p.product_id,
115 p.title,
116 p.price,
117 p.currency,
118
119 CASE
120 WHEN p.quantity <= 5 THEN 1
121 ELSE 0
122 END AS low_stock,
123
124 ( SELECT pi.image_url
125 FROM productimages pi
126 WHERE pi.product_id = p.product_id
127 ORDER BY pi.image_id
128 LIMIT 1) AS thumbnail
129
130FROM favorites f
131JOIN product p ON f.product_id = p.product_id;
132
133
134explain analyze
135SELECT *
136FROM vw_favorites
137where user_id = 5;
138
139--------------------------------------------------------
140------------------------------------------------------------
141-- преглед на сите ставки во кошничката на корисникот.
142CREATE OR REPLACE VIEW vw_cart_items AS
143SELECT
144 ci.cart_id,
145 ci.cart_item_id,
146
147 ci.product_id,
148 p.title,
149 p.price AS current_price,
150 p.currency,
151 ci.quantity,
152 ci.price_at_time,
153
154 (ci.quantity * ci.price_at_time) AS subtotal,
155
156 (
157 SELECT image_url
158 FROM productimages pi
159 WHERE pi.product_id = p.product_id
160 ORDER BY image_id
161 LIMIT 1
162 ) AS thumbnail
163
164FROM cartitems ci
165JOIN product p ON p.product_id = ci.product_id
166
167
168----------------------------------------------------
169-- сумиран приказ на кошничката.
170drop view vw_cart_summary;
171
172CREATE OR REPLACE VIEW vw_cart_summary AS
173SELECT
174 c.cart_id,
175 c.created_at,
176
177 c.user_id,
178
179 c.total_price AS cart_total,
180
181 COUNT(ci.cart_item_id) AS total_items,
182 SUM(ci.quantity) AS total_quantity
183
184FROM cart c
185JOIN cartitems ci ON ci.cart_id = c.cart_id
186
187GROUP BY c.cart_id;
188
189
190
191-------------------------------------------------
192-------------------------------------------------
193-- преглед на сите продукти кои припаѓаат на одредена нарачка
194drop view vw_order_items
195
196CREATE OR REPLACE VIEW v_order_items AS
197SELECT
198 oi.order_id,
199 oi.order_item_id,
200
201 oi.product_id,
202 p.title,
203 p.price AS current_price,
204 p.currency,
205 oi.quantity,
206 oi.price_at_time,
207
208 (oi.quantity * oi.price_at_time) AS subtotal,
209
210 (
211 SELECT image_url
212 FROM productimages pi
213 WHERE pi.product_id = p.product_id
214 ORDER BY image_id
215 LIMIT 1
216 ) AS thumbnail
217
218FROM orderitems oi
219JOIN product p ON p.product_id = oi.product_id;
220
221
222--------------------------------------------------------
223-- сумиран приказ на нарачка
224CREATE OR REPLACE VIEW v_order_summary AS
225SELECT
226 o.order_id,
227 o.created_at AS order_date,
228 o.status AS order_status,
229 o.total_price,
230
231 u.first_name || ' ' || u.last_name AS buyer_name,
232 ua.street || ' ' || ua.house_number AS street_address,
233 ua.city,
234
235 c.name AS carrier_name,
236 s.tracking_number,
237
238 (SELECT COUNT(*) FROM orderitems oi WHERE oi.order_id = o.order_id) AS total_items,
239 (SELECT SUM(quantity) FROM orderitems oi WHERE oi.order_id = o.order_id) AS total_quantity
240
241FROM "order" o
242JOIN appuser u ON u.user_id = o.buyer_id
243JOIN shipment s ON s.order_id = o.order_id
244JOIN carriers c ON c.carrier_id = s.carrier_id
245JOIN useraddress ua ON ua.user_address_id = s.user_address_id;
246
247---------------------------------------------------------------------------
248---------------------------------------------------------------------------
249-- dashboard приказ со статистики за продуктите на одреден продавач
250CREATE OR REPLACE VIEW v_seller_dashboard AS
251WITH active_package AS (
252 SELECT DISTINCT ON (seller_id)
253 seller_id,
254 package_id
255 FROM UserPackages
256 WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date
257 ORDER BY seller_id, end_date DESC
258)
259SELECT
260 u.user_id AS seller_id,
261 u.first_name,
262 u.last_name,
263 COUNT(DISTINCT pr.product_id) AS total_products,
264 SUM(oi.quantity) AS total_sales,
265 SUM(oi.quantity * oi.price_at_time) AS total_revenue,
266 ROUND(AVG(r.rating), 2) AS avg_rating,
267 pck.name AS package_name
268FROM AppUser u
269LEFT JOIN Product pr ON pr.seller_id = u.user_id
270LEFT JOIN OrderItems oi ON oi.product_id = pr.product_id
271LEFT JOIN Review r ON r.product_id = pr.product_id
272LEFT JOIN active_package ap ON ap.seller_id = u.user_id
273LEFT JOIN Package pck ON pck.package_id = ap.package_id
274WHERE u.is_verified = 1
275GROUP BY u.user_id, u.first_name, u.last_name, pck.name;
276
277
278
279EXPLAIN ANALYZE
280SELECT *
281FROM v_seller_dashboard
282WHERE seller_id = 120824;
283
284
285--------------------------------------------------------------
286-- преглед на кориснички профил
287
288CREATE OR REPLACE VIEW v_user_profile as
289
290WITH primary_location AS (
291 SELECT user_id, city || ', ' || country AS location
292 FROM UserAddress
293 WHERE is_primary = 1
294),
295user_orders AS (
296 SELECT buyer_id, COUNT(*) AS total_orders
297 FROM "order"
298 GROUP BY buyer_id
299)
300SELECT
301 u.user_id,
302 u.first_name,
303 u.last_name,
304 u.email,
305 u.phone_number,
306 pl.location AS primary_location,
307 uo.total_orders,
308 u.created_at
309FROM AppUser u
310LEFT JOIN primary_location pl ON pl.user_id = u.user_id
311LEFT JOIN user_orders uo ON uo.buyer_id = u.user_id;
312
313
314
315explain analyze
316SELECT * FROM v_user_profile
317WHERE user_id = 600;
318