CREATE TABLE IF NOT EXISTS "user"();
WITH
balance(userid, date, balance_amount) AS (
VALUES ('1112879', DATE '2022-08-05',700),
('1112879', DATE '2022-08-15',1500),
('1112879', DATE '2022-08-18',10100),
('1112879', DATE '2022-08-22',1700),
('1112879', DATE '2022-09-01',4200)
)
select
day::date,
(select balance_amount from balance as t where t.userid = balance.userid and (day::date - t.date::date)>=0 ORDER BY abs(day::date - t.date::date) asc limit 1) as amount,
balance.userid
from
generate_series('2022-08-01'::date, now()::date, '1 day'::interval) as day
cross join (select userid from balance group by userid) as balance
ORDER BY day;
WITH RECURSIVE r AS (
SELECT id, parent_id, name, 1 as level, parent_id as first_level
FROM geo
WHERE parent_id = 4
UNION
SELECT geo.id, geo.parent_id, geo.name, (r.level+1), r.first_level
FROM geo
JOIN r
ON geo.parent_id = r.id
)
SELECT * FROM r;
select * from table inner join (select a, b,c from table group by a, b, c having count(*) > 1) as t on t.a = table.a and t.b = table.b and t.c = table.c;
Я поэтому и задал этот вопрос, чтобы люди поделились опытом, подсказали как это лучше сделать
select users.name, bank_props.bik, bank_props.inn, bank_props.kpp, bank_props.individual_guid, settlements.period
from users
join
(select max(period) as date, user_id from bank_props where active = true group by user_id) as t_bank
on (users.id = t_bank.user_id)
join bank_props on t_bank.user_id = bank_props.user_id and bank_props.period = t_bank.date
join
(select max(period) as date, user_id from settlements where status = 'COMPLETED' group by user_id) as t_settlements
on (users.id = t_settlements.user_id)
join settlements on t_settlements.user_id = settlements.user_id and settlements.period = t_settlements.date;
with t_check as (
select coalesce((select DATE_PART('day', (date_pay::timestamp - now()::timestamp))>=0 from t where user_id = 1 limit 1), true) as is_added
)
INSERT INTO t (user_id, date_pay)
VALUES (1, '2022-09-01 00:00:00')
ON CONFLICT (user_id) DO UPDATE set date_pay = (
case when DATE_PART('day', (date_pay::timestamp - now()::timestamp)) <= 0
then date_pay + interval '31 day'
else now() + interval '31 day' end
) returning (select is_added from t_check limit 1);
create sequence user_document_number;
CREATE TABLE documents(
id SERIAL,
number INT NOT NULL DEFAULT(nextval('user_document_number')),
text text,
PRIMARY KEY(id)
);
insert into documents(text) values('assaassa');
SELECT id,
IF((SELECT COUNT(*) from devices where devices.name = call_list.num) = 0, call_list.num, call_list.num2) "client_num",
FROM call_list;
select
call_list.id,
t.count
from call_list
join (select count(*) as count, devices.name from devices group by devices.name) as t
on t.name = call_list.num;