Для каждой группы подсчитайте определенные строки, возникающие до первой другой конкретной строки с помощью CodeIgniter. ⇐ Php

[Anonymous]

Я работаю над тем, чтобы выяснить, сколько в среднем сообщений отправляется потенциальным клиентам, прежде чем они навсегда откажутся от наших сообщений. В нашей таблице активности тип активности 6 — это отправленное сообщение, а тип активности 9 — отказ. Таким образом, данные таблицы могут выглядеть примерно так:

Код: Выделить всё

+---------+-------------+---------------+---------------+
| Lead Id | Activity ID | Activity Type | Activity Data |
+---------+-------------+---------------+---------------+
| 1       | 12345       | 6             | 1/1/2021      |
+---------+-------------+---------------+---------------+
| 1       | 12346       | 6             | 1/2/2021      |
+---------+-------------+---------------+---------------+
| 1       | 12347       | 10            | 1/3/2021      |
+---------+-------------+---------------+---------------+
| 1       | 12348       | 6             | 1/3/2021      |
+---------+-------------+---------------+---------------+
| 1       | 12349       | 6             | 1/4/2021      |
+---------+-------------+---------------+---------------+
| 1       | 123410      | 9             | 1/5/2021      |
+---------+-------------+---------------+---------------+

Мне уже удалось определить среднее количество отправок записей в течение заданного периода времени, и я попытался изменить этот запрос, чтобы учесть действия по отказу от участия, с помощью подзапроса в предложенииwhere. Однако это не приносит никаких результатов. Это было основано на публикации здесь: Как подсчитать количество строк перед заданной строкой в ​​MySQL с помощью CodeIgniter?

Код: Выделить всё

define macro counts
(
SELECT
COUNT(CASE WHEN activity_type_id = 6 THEN activity_type_id END) as sends,
lead_id
FROM data.Lead
JOIN data.Activity USING (lead_id)
WHERE activity_id < (SELECT activity_id FROM data.Activity WHERE activity_type_id = 9)
AND activity_date BETWEEN '2020-01-01' AND '2020-3-31'
GROUP BY 2
);

SELECT avg(sends) as avgsends
FROM $counts;

Для вывода он должен подсчитать общее количество типов активности «6», только если они имеют значение типа активности «9», и усреднить их. Таким образом, функция выполняет несколько задач:
а) Ищет записи интересов, которые имеют значение активности "9".
б) Если да, подсчитывает количество значений "6" до значения "9".
в) Усредняет количество по всем интересам за заданный период времени.

Подробнее здесь: https://stackoverflow.com/questions/669 ... r-specific