SQL Developer
Полный ответ GPT
Ответы
Ниже — сквозной пример **users → orders → payments** (как для учебных задач, так и для собеседований). Есть: создание таблиц, данные, связи, типичные кейсы (частичная оплата, отмена, переплата).
---
## 1) Схема (DDL)
```sql
CREATE SCHEMA IF NOT EXISTS shop;
SET search_path TO shop, public;
-- 1) Пользователи
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 2) Заказы
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
status TEXT NOT NULL CHECK (status IN ('new','paid','cancelled','refunded')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 3) Платежи (один заказ может иметь несколько платежей: частичная оплата, доплата)
CREATE TABLE payments (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
payment_date TIMESTAMPTZ NOT NULL DEFAULT now(),
payment_type TEXT NOT NULL CHECK (payment_type IN ('card','cash','bank_transfer','apple_pay')),
paid_amount NUMERIC(12,2) NOT NULL CHECK (paid_amount > 0)
);
-- Индексы под частые запросы
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
CREATE INDEX idx_payments_order_date ON payments(order_id, payment_date DESC);
```
---
## 2) Пример данных (INSERT)
```sql
INSERT INTO users(full_name, email, city) VALUES
('Ivan Petrov', 'ivan@mail.com', 'Vilnius'),
('Anna Ivanova', 'anna@gmail.com', 'Kaunas'),
('No Orders', 'no@ex.com', 'Klaipeda'),
('Partial Pay', 'pp@ex.com', 'Vilnius');
-- orders: сделаем разные статусы и суммы
INSERT INTO orders(user_id, order_date, amount, status) VALUES
(1, '2025-03-01', 120.00, 'paid'), -- полностью оплачен
(1, '2025-03-05', 80.00, 'new'), -- еще не оплачен
(2, '2025-03-02', 250.00, 'new'), -- будет частичная оплата
(2, '2025-03-03', 50.00, 'cancelled'), -- отменен (платежей нет)
(4, '2025-03-04', 300.00, 'new'); -- будет переплата
-- payments: частичная оплата, полная, переплата
INSERT INTO payments(order_id, payment_date, payment_type, paid_amount) VALUES
(1, '2025-03-01 10:00+00', 'card', 120.00), -- order 1 полностью
(3, '2025-03-02 12:00+00', 'bank_transfer', 100.00), -- order 3 частично
(3, '2025-03-06 09:00+00', 'card', 150.00), -- order 3 доплата => итого 250
(5, '2025-03-04 11:00+00', 'card', 320.00); -- order 5 переплата (на 20)
```
---
## 3) Что можно проверять на этом примере (типовые задачи)
### 3.1 Пользователи без заказов
```sql
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
```
### 3.2 Заказы с суммой оплат (агрегация)
```sql
SELECT
o.id,
o.user_id,
o.amount,
o.status,
COALESCE(SUM(p.paid_amount), 0) AS paid_total
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
GROUP BY o.id, o.user_id, o.amount, o.status
ORDER BY o.id;
```
### 3.3 Найти проблемные заказы: недоплата / переплата
```sql
WITH paid AS (
SELECT o.id, o.amount, COALESCE(SUM(p.paid_amount), 0) AS paid_total
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
GROUP BY o.id, o.amount
)
SELECT
id,
amount,
paid_total,
(paid_total - amount) AS delta
FROM paid
WHERE paid_total <> amount
ORDER BY id;
```
### 3.4 Обновить статус заказа на `paid`, если оплачено полностью
```sql
UPDATE orders o
SET status = 'paid'
FROM (
SELECT o2.id
FROM orders o2
LEFT JOIN payments p ON p.order_id = o2.id
GROUP BY o2.id, o2.amount
HAVING COALESCE(SUM(p.paid_amount), 0) >= o2.amount
) x
WHERE o.id = x.id;
```
### 3.5 “Последний платеж по заказу” (окно)
```sql
SELECT *
FROM (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY p.order_id ORDER BY p.payment_date DESC) AS rn
FROM payments p
) t
WHERE rn = 1
ORDER BY order_id;
```
---
## 4) Мини-легенда (чтобы было “сквозно”)
* `users` — клиенты.
* `orders` — заказы клиента (может быть много).
* `payments` — платежи по заказу (может быть 0..N).
* Есть кейсы:
* **без заказов** (`No Orders`)
* **частичная оплата** (`order_id = 3`)
* **переплата** (`order_id = 5`)
* **отмененный заказ** без платежей (`order_id = 4`)
Если хочешь, я на этом же наборе данных сделаю “план по PostgreSQL командам” (DDL/DML/индексы/EXPLAIN) именно под твой формат шпаргалки/уроков.