---

? ???? ?? ???
???? ???? ? ????

---

??? ???

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

?? ? ???? ??? ???? ? ? ?? ??? ? ??? ?? ????.
?? ??? ?? ??? ? ????.

• post_like ???? post_id user_id ? ?? ?? ?? ??? ?????.
• ?? post_like ???? id ?? ???? post_id user_id? ?? ?? ?? ?????.

??? ?? ??? ?? ???? ??? ??? ???? ?? ??? ???? ???.

??? ??? ?????

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

? ??? ??? 2? ??? 3? ? ? ??, ????? 2? ???? ?? ?? ?????.

?? ??

?? ?? ??? ??? ????? ??? ? ????.

? ????? ? ??? ????.

• ?? ??
• ?? ?? ??(?? ??)
• ?? ??(?? ??)

?? ??

?? ??

?? ???? ???? ?? ??? ????? ?? ??? ???? ??? ??? ???? ?? ??? ?????.

??? ?? ??? ???? ???
?? ??????(?:
)?? ?? ? ?? "?? ??" ?? rsync).

CTE

CTE? ?? DX? ???? ??? ?????.

CTE? ???? ??? ???? ??? ?? ???? ??? ? ?? ??? ??? ???? ??? ? ????.

? ?? ??? ?? ??? ???? ??? ? ????? ???? ??? ?????.

CTE ???

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

????? CTE? ?? ???? ??? ??? ? ????.

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

group_index? 2? ??? ?? ?? ? ?? post_id = 3?? user_id = 2? ???? ? ?? ??? ?????.

??? ??? ????

row_number() over (partition by ...) as group_index? ?? partition by ?? ?? ???? ?? ???? ?? ?? ???? ???? ? ?? ??? ???? ? ?????. ????.

partition? ?? ?? ???? ?? ?????? ??? group by? ?????, group by? ? ??? ?? 1?? ?? ???? ?? partition? ???? ??? ???? ?? ???? ? ?? ??? ? ????.

group_index? ? ?? ??, ?? SQL ?????.

??? ????? ???

?? group_index > 1? ?? ?? ??? ? ?? ?? ???? ??, ? ?? ???? ?????.

create table "post" (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

create table "user" (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
)

create table "post_like" (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES post(id),
  user_id INTEGER NOT NULL REFERENCES user(id)
)

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

ID? 4? ? ?? ???? ???.

?? ?? - ??? ??

?? ? ?? cte Duplicates_info?? ?? ?? post_like ????? ??? ?? ??? ?? ?????.
??? ??? cte Duplicates_info? ???? ?? ??? ID? ????.

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

????? ??? ?????.

???? ??? ? ??? ??? ????.

 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

? ??? ??? ??? ????? ????? ?????.

??? ?? ?? ?? ?? ??? ??? ?? ??? ??????? ???????? ???? ????.

?? ?? - ?? ??

??? ??? ?? ??? ??? ? ????.
???? ?? ?? ??? ???? ?? ??? ??????? ????? ???.

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database 

?? ??

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

??

?? ?? ? ??? ??? ??? ??? ???? ??? ??? ?? ?? ??? ???.

? ?? ??? ???? ???? ?????

?? ??? ?? ????? ??????

??? ?????!

? ??? Postgres SQL?? ??? ???? ??? ?? ?????. ??? ??? PHP ??? ????? ?? ?? ??? ?????!