У меня есть рабочая задача — написать SQL-запрос, который покажет, сколько активных клиентов являются новыми (= у них нет предыдущих транзакций), а не вернувшимися за определенный период.
В БД нет ничего особенного, чтобы получить это, поэтому мое решение принимает транзакцию и сравнивает customer_id
в бассейн customer_id
s, которые относятся ко всем транзакциям, которые имели место до этого. Вот как это выглядит:
SELECT allthem.period, allthem.c "all", newonly.c "new", (allthem.c - newonly.c) "returning"
FROM (
SELECT date_trunc('week', t.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
FROM transactions t
WHERE t.status="paid" AND (t.price->>'payment_total')::real > 35
GROUP BY date_trunc('week', t.paid::timestamptz)
ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS allthem
JOIN (
SELECT date_trunc('week', b.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
FROM transactions t
WHERE t.status="paid" AND (t.price->>'payment_total')::real > 35
AND t.customer_id NOT IN (SELECT customer_id FROM transactions WHERE status="paid" AND (price->>'payment_total')::real > 35 AND paid::timestamptz < t.paid::timestamptz)
GROUP BY date_trunc('week', t.paid::timestamptz)
ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS newonly ON allthem.period=newonly.period
WHERE allthem.period > date_trunc('week', now()::timestamptz at time zone 'pst') - interval '12 months'
Это работает, но проблема в том, что это довольно медленно.
Есть ли способ вычислить необходимые данные с меньшей нагрузкой на сервер?