Python
Junior
a
как еще можно решить
```sql
WITH
user_orders AS (
SELECT
user_id,
COUNT(*) AS order_count
FROM
orders
GROUP BY
user_id
),
active_users AS (
SELECT
user_id,
name
FROM
users
WHERE
last_login > NOW() - INTERVAL '30 days'
)
SELECT
au.name,
uo.order_count
FROM
active_users au
JOIN user_orders uo
ON au.user_id = uo.user_id;
```
Ответы
Вот несколько альтернативных способов решить этот запрос без CTE:
1. Подзапросы в FROM (Derived Tables)
```sql
SELECT au.name, uo.order_count
FROM (
SELECT user_id, name
FROM users
WHERE last_login > NOW() - INTERVAL '30 days'
) au
JOIN (
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
) uo ON au.user_id = uo.user_id;
```
2. Оконные функции (если нужно сохранить все строки)
```sql
SELECT
u.name,
COUNT(o.id) OVER(PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name, o.id;
-- или более оптимально:
SELECT DISTINCT
u.name,
COUNT(o.id) OVER(PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days';
```
3. Коррелированный подзапрос
```sql
SELECT
u.name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u
WHERE u.last_login > NOW() - INTERVAL '30 days';
```
4. LATERAL соединение
```sql
SELECT
u.name,
uo.order_count
FROM users u
CROSS JOIN LATERAL (
SELECT COUNT(*) as order_count
FROM orders
WHERE user_id = u.id
) uo
WHERE u.last_login > NOW() - INTERVAL '30 days';
```
5. JOIN с агрегацией в основном запросе
```sql
SELECT
u.name,
COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name;
```
6. Функция
```sql
CREATE OR REPLACE FUNCTION get_user_order_count(user_id INT)
RETURNS INT AS $$
DECLARE
count INT;
BEGIN
SELECT COUNT(*) INTO count
FROM orders
WHERE orders.user_id = $1;
RETURN count;
END;
$$ LANGUAGE plpgsql;
SELECT
name,
get_user_order_count(id) as order_count
FROM users
WHERE last_login > NOW() - INTERVAL '30 days';
```
7. Представление (VIEW)
```sql
CREATE VIEW v_user_orders AS
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;
SELECT u.name, uo.order_count
FROM users u
JOIN v_user_orders uo ON u.id = uo.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days';
```
8. Временная таблица
```sql
CREATE TEMP TABLE tmp_user_orders AS
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;
CREATE INDEX ON tmp_user_orders(user_id);
SELECT u.name, tuo.order_count
FROM users u
JOIN tmp_user_orders tuo ON u.id = tuo.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days';
DROP TABLE tmp_user_orders;
```
Сравнение производительности:
Для маленьких/средних данных:
```sql
-- Оптимальный вариант
SELECT
u.name,
COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name;
```
Для больших данных с активными пользователями:
```sql
-- Оптимизация: сначала фильтрация, потом JOIN
SELECT
u.name,
COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days';
```
Самый быстрый для большого количества заказов:
```sql
-- С коррелированным подзапросом (если есть индекс на orders.user_id)
SELECT
u.name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u
WHERE u.last_login > NOW() - INTERVAL '30 days';
```
Бенчмарк-советы:
1. Индексы критически важны:
```sql
CREATE INDEX idx_users_last_login ON users(last_login);
CREATE INDEX idx_orders_user_id ON orders(user_id);
```
1. EXPLAIN ANALYZE покажет лучший вариант:
```sql
EXPLAIN ANALYZE
SELECT ... -- ваш запрос
```
1. Для сложных сценариев используйте MATERIALIZED VIEW:
```sql
CREATE MATERIALIZED VIEW mv_daily_user_stats AS
SELECT
u.id,
u.name,
COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.last_login > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name;
-- Обновлять по расписанию
REFRESH MATERIALIZED VIEW mv_daily_user_stats;
```
Выбор метода зависит от:
· Объема данных
· Частоты выполнения запроса
· Требований к актуальности данных
· Наличия индексов