XLnryNNi

1-- ---
2-- Более полная группировка
3select supplier_id, category_id, sum(units_in_stock)
4from products
5group by grouping sets ((supplier_id), (supplier_id, category_id))
6order by supplier_id, category_id;
7
8select supplier_id, category_id, sum(units_in_stock)
9from products
10group by grouping sets ((supplier_id), (supplier_id, category_id))
11order by supplier_id, category_id nulls first;
12-- ---
13select supplier_id, category_id, sum(units_in_stock)
14from products
15group by rollup (supplier_id, category_id)
16order by supplier_id, category_id nulls first;
17-- (1, null, 30) = (1, 1, 17) + (1, 2, 13) (17 + 13 = 30
18
19select supplier_id, category_id, reorder_level, sum(units_in_stock)
20from products
21group by rollup (supplier_id, category_id, reorder_level)
22order by supplier_id, category_id nulls first;
23
24-- CUBE формирует результаты для всех возможных значений
25select supplier_id, category_id, sum(units_in_stock)
26from products
27group by cube (supplier_id, category_id)
28order by supplier_id, category_id nulls first;
29
30select supplier_id, category_id, reorder_level, sum(units_in_stock)
31from products
32group by cube (supplier_id, category_id, reorder_level)
33order by supplier_id, category_id nulls first;
34
35-- ДЗ заскринено
36
37-- ---------------------------------------------
38-- ---------------------------------------------
39-- ---------------------------------------------
40
41-- Оконные функции
42
43-- Окно это выражение или инструкция описывающая набор строк который будет обрабатывать функция
44-- Это выражение описывает и порядок обработки
45
46-- Позволяют обрабатывать группы строк без образования группировок
47-- Делятся на агрегатные и ранжирования
48-- Отрабатывают после join, where, group by, having, но перед order by
49
50-- Синтаксис: function OVER (expression)
51
52-- Посмотреть средние цены по категориям
53select category_id, avg(unit_price) as avg_price
54from products
55group by category_id
56limit 5;
57
58select category_id, category_name, product_name,
59  unit_price, avg(unit_price) over (partition by category_id) as avg_price
60from products
61join categories using (category_id);
62
63-- Сделаем окно: группировка по order_id и посчитать нарастающий итог
64select order_id, order_date, product_id, customer_id, unit_price as sub_total,
65  sum(unit_price) over (partition by order_id order by product_id) as sale_sum
66from orders
67join order_details using (order_id)
68order by order_id;
69-- Посчитать нарастающий итог по всем заказам
70select order_id, order_date, product_id, customer_id, unit_price as sub_total,
71  sum(unit_price) over (order by row_id) as sale_sum
72from (select order_id, order_date, product_id, customer_id, unit_price, row_number() over () as row_id
73	  from orders
74	  join order_details using (order_id)) subquery
75order by order_id;
76
77-- ---
78-- Ранжирование
79-- ---
80-- RANK() выстроит по порядку с уникальными значениями
81-- Проранжировать все продукты
82select product_name, units_in_stock, rank() over (order by product_id)
83from products;
84
85-- Проранжировать продукты, каждая новая категория начинается с единицы
86select product_name, category_id, units_in_stock, rank() over (partition by category_id order by product_id)
87from products;
88
89-- Ранжирование без гэпов:
90select product_name, units_in_stock, dense_rank() over (order by units_in_stock)
91from products; -- 1, 1, 1, 1, 1, 2 (!)
92
93-- Ранжирование с гэпами:
94select product_name, units_in_stock, rank() over (order by units_in_stock)
95from products; -- 1, 1, 1, 1, 1, 6 (!) ибо первых (равнозначных) записей пять
96
97-- Ранжировать значения через логические операторы:
98select product_name, unit_price,
99  dense_rank() over (
100	  order by
101	    case
102	      when unit_price >= 81 then 1 
103	      when unit_price >= 30 and unit_price < 81 then 2
104	      else 3
105	    end
106  ) as ranking
107from products
108order by unit_price desc;
109
110-- Отличие текущей цены от предыдущей:
111select product_name, unit_price,
112  lag(unit_price) over (order by unit_price desc) as previous_price,
113  lag(unit_price) over (order by unit_price desc) - unit_price as price_lag
114from products
115order by unit_price desc;
116-- LAG(column) подставляет значение предыдущей строки
117-- LEAD(column) наоборот подставляет значение следующей строки
118-- В них можно передавать смещение вторым аргументом, например, чтобы "отставать" на две и более строки
119
120-- Анализ данных с временными метками:
121select product_name, unit_price,
122  LEAD(unit_price, 2) over (order by unit_price) - unit_price as price_lag
123from products
124order by unit_price;
125
126-- ---
127-- Возврат N-записей
128---
129
130-- Отсортировать по цене и вывести только 3 записи
131select * from products
132where product_id = any(
133	select product_id
134	from (
135		select product_id, unit_price, row_number() over (order by unit_price desc) as nth
136		from products
137	) sorted_prices
138	where nth < 4
139);
140
141select * from (
142	select product_id, product_name, category_id, unit_price, units_in_stock, row_number() over (order by unit_price desc) as nth
143	from products
144) as sorted_prices
145where nth < 4
146order by unit_price;
147
148-- ДЗ заскринено
149
150-- --------------------------
151-- Транзакции
152-- --------------------------
153
154-- Управление транзакциями: TCL - Transaction Control Language
155-- BEGIN / COMMIT
156-- ROLLBACK
157-- SAVEPOINT savepoint_name
158-- ROLLBACK TO savepoint_name
159
160begin;
161	with prod_update as (
162	  update products
163	  set discontinued = 1
164	  where units_in_stock < 10
165	  returning product_id
166	)
167	select * into last_orders_on_discontinued
168	from order_details
169	where product_id in (select product_id from prod_update);
170commit;
171select * from last_orders_on_discontinued;
172
173-- Управление изоляцией транзакций
174begin;
175
176	with prod_update as (
177	  update products
178	  set discontinued = 1
179	  where units_in_stock < 10
180	  returning product_id
181	)
182	select * into last_orders_on_discontinued
183	from order_details
184	where product_id in (select product_id from prod_update);
185	drop table last_orders_on_discontinued1;
186	
187commit;
188
189select * from last_orders_on_discontinued;
190drop table if exists last_orders_on_discontinued;
191
192-- Явный rollback
193begin;
194
195	with prod_update as (
196	  update products
197	  set discontinued = 1
198	  where units_in_stock < 10
199	  returning product_id
200	)
201	select * into last_orders_on_discontinued
202	from order_details
203	where product_id in (select product_id from prod_update);
204	delete from order_details
205	where product_id in (select product_id from last_orders_on_discontinued); -- случайно удалили нужное
206	
207select count(*) from order_details; -- проверим транзакцию
208rollback;
209select count(*) from order_details; -- записей стало больше, ибо мы откатили транзакцию, в которой удалили строки
210
211-- Резервное копирование
212drop table if exists last_orders_on_discontinued;
213begin transaction isolation level serializable;
214
215with prod_update as (
216	update products
217	set discontinued = 1
218	where units_in_stock < 10
219	returning product_id
220)
221select * into last_orders_on_discontinued
222from order_details
223where product_id in (select product_id from prod_update);
224
225savepoint backup;
226
227delete from order_details
228where product_id in (select product_id from last_orders_on_discontinued);
229
230-- select count(*) from order_details;
231rollback to backup;
232-- select count(*) from order_details;
233
234update order_details
235set quantity = 0
236where product_id in (select product_id from last_orders_on_discontinued);
237
238commit;
239
240select count(*) from order_details;
241select * from last_orders_on_discontinued;
242
243select *
244from order_details
245where product_id in (select product_id from last_orders_on_discontinued);
246
247drop table if exists last_orders_on_discontinued;
248
249-- ---
250-- Задача: проиндексировать зарплату рабочим. У кого < 40000, умножить на 1.5
251begin;
252
253alter table employees
254add column salary decimal(12, 2);
255
256update employees set salary = random() * 100;
257commit;
258
259select employee_id, last_name, first_name, salary from employees;
260
261begin;
262	update employees
263	set salary = salary * 1.5
264	where salary < 50;
265savepoint increase_salary;
266	update employees
267	set salary = salary * 0.8
268	where salary > 70;
269rollback to increase_salary;
270	update employees
271	set salary = 0
272	where employee_id = 9;
273commit;
274
275-- ДЗ заскринено
276
277-- -------------------------------------------
278-- -------------------------------------------
279-- Событийное программирование
280-- -------------------------------------------
281-- -------------------------------------------
282
283-- Логирование временной метки последнего изменения строк таблицы
284alter table customers add column last_updated timestamp;
285
286create or replace function track_changes_on_customers() returns trigger as $$
287begin
288	new.last_updated = now();
289	return new;
290end
291$$ language plpgsql;
292
293drop trigger if exists customers_timestamp on customers;
294create trigger customers_timestamp before insert or update on customers
295	for each row execute procedure track_changes_on_customers();
296
297select customer_id, company_name, contact_name, last_updated from customers order by customer_id;
298
299update customers set contact_name='Anna Trujillo' where customer_id = 'ANATR';
300
301select customer_id, company_name, contact_name, last_updated from customers order by customer_id;
302
303-- Задача: логировать пользователя
304alter table employees
305add column user_changed text;
306
307create or replace function track_changes_on_employees() returns trigger as $$
308begin
309	new.user_changed = session_user;
310	return new;
311end
312$$ language plpgsql;
313
314drop trigger if exists employees_user_change on employees;
315create trigger employees_user_change before insert or update on employees
316	for each row execute procedure track_changes_on_employees();
317
318select employee_id, last_name, first_name, user_changed from employees order by employee_id;
319
320update employees set first_name='Martha' where employee_id = 1;
321
322select employee_id, last_name, first_name, user_changed from employees order by employee_id;
323
324-- Аудит таблицы. Хотим оставлять след всех insert, update, delete в отдельной таблице
325drop table if exists products_audit;
326create table products_audit (
327	op char(1) not null,
328	user_changed text not null,
329	time_stamp timestamp not null,
330	
331	product_id smallint not null,
332	product_name varchar(40) not null,
333	supplier_id smallint,
334	category_id smallint,
335	quantity_per_unit varchar(20),
336	unit_price real,
337	units_in_stock smallint,
338	units_on_order smallint,
339	reorder_level smallint,
340	discontinued integer not null
341);
342
343create or replace function build_audit_products() returns trigger as $$
344begin
345	if tg_op = 'INSERT' then
346		insert into products_audit
347		select 'I', session_user, now(), nt.* from new_table nt;
348	elseif tg_op = 'UPDATE' then
349		insert into products_audit
350		select 'U', session_user, now(), nt.* from new_table nt;
351	elseif tg_op = 'DELETE' then
352		insert into products_audit
353		select 'D', session_user, now(), ot.* from old_table ot;
354	end if;
355	return null;
356end
357$$ language plpgsql;
358
359drop trigger if exists audit_products_insert on products;
360create trigger audit_products_insert after insert on products
361	referencing new table as new_table
362	for each statement execute procedure build_audit_products();
363
364drop trigger if exists audit_products_update on products;
365create trigger audit_products_update after update on products
366	referencing new table as new_table
367	for each statement execute procedure build_audit_products();
368
369drop trigger if exists audit_products_delete on products;
370create trigger audit_products_delete after delete on products
371	referencing old table as old_table
372	for each statement execute procedure build_audit_products();
373
374select * from products_audit;
375insert into products values (83, 'Russian Mozarella', 7, 4, '200g per package', 50, 20, 0, 0, 0);
376select * from products_audit;
377update products set quantity_per_unit='150g per package' where product_id=83;
378select * from products_audit;
379delete from products where product_id=83;
380select * from products_audit;
381
382-- ДЗ заскриншотено
383
384-- На самостоятельной: функции, триггеры, транзакции, продвинутая группировка, оконные функции
385
386-- ------------------------
387-- +----------------------+
388-- |     Безопасность     |
389-- +----------------------+
390-- ------------------------
391
392create role sales_stuff;
393create role northwind_admins;
394
395create user john_smith with password 'qwerty';
396create user north_admin1 with password 'qwerty';
397
398-- Эти две команды обязательны чтобы убрать права изменения схемы данных у пользователей
399revoke create on schema public from public;
400revoke all on database northwind from public;
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431create user john_smith with password 'qwerty';
432create user north_admin1 with password 'qwerty';
433
434revoke create on schema public from public;
435revoke all on database northwind from public;
436
437-- -------------------
438-- Права на уровне БД и схемы
439
440create role sales_stuff;
441
442grant connect on database northwind to sales_stuff; -- роль
443grant connect on database northwind to northwind_admins; -- роль
444
445grant usage on schema public to sales_stuff;
446
447grant usage on schema public to northwind_admins;
448grant create on schema public to northwind_admins;
449grant create on database northwind to northwind_admins;
450
451grant sales_stuff to john_smith;
452grant northwind_admins to north_admin1;
453
454-- --------------------
455-- Права на уровне таблиц
456select grantee, privilege_type
457from information_schema.role_table_grants
458where table_name = 'admin_demo2';
459
460grant select, insert, update, delete on table
461	public.orders,
462	public.order_details,
463	public.products
464to sales_stuff;
465grant select on table public.employees to sales_stuff;
466
467grant select, insert, update, delete, truncate, references, trigger
468on all tables in schema public
469to northwind_admins;
470
471-- ----------------------
472-- Права на уровне столбцов
473
474revoke select on employees from sales_stuff;
475
476grant select (employee_id, last_name, first_name, title)
477	on employees
478	to sales_stuff;
479
480-- ------------------------
481-- Права на уровне строк
482
483-- northwind=> select count(*) from products;
484--  count
485-- -------
486--     82
487-- (1 строка)
488
489alter table products
490	enable row level security;
491	
492-- count
493-- -------
494--      0
495-- (1 строка)
496
497create policy active_products_for_sales_stuff on products
498for select
499to sales_stuff
500using (discontinued <> 1);
501
502-- northwind=> select count(*) from products;
503--  count
504-- -------
505--     63
506-- (1 строка)
507
508-- Проверка:
509select count(*) from products where discontinued <> 1; -- count = 63
510
511create policy reordered_products_for_sales_stuff on products
512for select
513to sales_stuff
514using (reorder_level > 10);
515
516-- northwind=> select count(*) from products;
517--  count
518-- -------
519--     69
520-- (1 строка)
521
522drop policy reordered_products_for_sales_stuff on products;
523
524-- northwind=> select count(*) from products;
525--  count
526-- -------
527--     63
528-- (1 строка)
529
530select * from pg_roles;
531
532revoke all privileges on employees, orders, order_details, products from sales_stuff;
533revoke all on database northwind from sales_stuff;
534revoke all on schema public from sales_stuff;
535
536drop policy active_products_for_sales_stuff on products;
537
538drop role sales_stuff;
539
540select * from pg_roles;
541
542-- --------------------------------------------------------------------------------------------------------------------------------------------
543
544-- Импорт данных в базу
545
546-- Зайти на сайт https://www.kaggle.com/datasets
547-- Выбрать какой-нибудь датасет с минимум 5 столбцами, желательно 8-10
548-- Нажать кнопку Download
549-- Залогиниться, можно через Google, но имя написать английскими буквами
550
551-- Структура таблицы должна совпадать со столбцами в CSV
552create table spotify_hits
553(
554	artist text,
555	song text,
556	duration_ms int,
557	explicit varchar(5),
558	year smallint, 
559	popularity smallint,
560	danceability real,
561	energy real,
562	key smallint,
563	loudness real,
564	mode smallint,
565	speechiness real,
566	acousticness real,
567	instrumentalness real,
568	liveness real,
569	valence real,
570	tempo real,
571	genre text
572);
573
574-- Команда импорта:
575-- 1. Сначала войти в psql.exe название_бд пользователь
576-- 2. Внутри psql (должна быть строчка "название_бд=#"): команда
577--    \copy имя_таблицы from 'C:\путь_до_csv_файла' delimiter ',' csv header
578
579-- После импорта:
580
581select * from spotify_hits where artist = 'Post Malone';
582
583