Как правильно оформить SQL запрос для аналитики в каналах?

Question

[twink132]

Проектирую бота в функционал, которого входит статистика по обращениям в каналах филиала и отправка сообщений работников в канал в зависимости от выбранного филиала регистрации.
Структура базы данных:

Таблица "Users": id, user_id (Telegram ID пользователя), user_name (first_name в телеграм),user_surname (second name в телеграм), MRF (филиал, который пользователь выбирает при первой регистрации)
Таблица "For Posts": id, username (first name + last name пользователя), user_id (айди пользователя), created_at (время создания поста по UNIX TIME), text (текст сообщения, если был), message (message.id в канале), chanel_id (айди канала куда отправилось обращение)
Таблица "For Reply":id, username (firstname +last name пользователя), user_id (айди пользователя), reated_at (время создания поста по UNIX TIME), text (текст сообщения, если был), reply to id (message.id сообщения в канале в котором пишется комментарий), message (message.id комментария на пост), chanel_id (айди канала куда отправилось обращение).
Словари:
managers (занесены user id диспетчеров и их ФИО для отображения их в отчетах),
channels (channel_id каналов для обращений и их название).
Виды аналитики:
Общая (обращения по всем каналам)
По сотрудникам (статистика отработки обращений по ФИО сотрудников во всех каналах)
По каналам (статистика отработки обращений сотрудниками по каждому каналу)
По выбранному каналу (статистика отработки обращений сотрудниками по каждому каналу)
Как считается статистика:
Интервал: дата статистики которая необходима пользователю
ФИО: ФИО сотрудника отрабатывающего обращение
Всего сообщений: обращения взятые в работу до 30 секунд + после 30 секунд
До 30 секунд: обращения где диспетчер ответил в комментариях к посту ДО 30 секунд
После 30 секунд: обращения где диспетчер ответил в комментариях к посту ПОСЛЕ 30 секунд
SL % (Service level): Доля обращений взятых в работу до 30 секунд
Проблемы:
1)Сообщения по кол-ву верно считаются в статистики по КАНАЛАМ, но при этом ВСЕобращения засчитываются как до 30 секунд.
2) В отчете по сотрудникам считаются не все обращения за выбранный период, а только половину и них. В БД message.id постов часто пересекаются с другими каналами (всего каналов 8), насколько я понял разработчики не сделали их индивидуальными как user_id или chanel_id.
3) Поможет ли вайп БД Sqlite? БД формируется уже 2 месяца, возможно Message.id в разных канал пересекаются из-за этого (в функции сохранения ответа на тикет есть условия, которое дает сохранять ток первый ответ диспетчера (учитывается как обращение которое взяли в работу).

Возможно, у кого-то был опыт с решением подобных кейсов, код отвечающий за аналитику в комментариях ниже.

Comments

[twink132]

Программный код, который отвечает за аналитику:

spoiler

Сохранение обращения (поста) в БД

def save_bot_repost_info(messageFromSender, forwardedMessage): ## Создание поста (время создания) 
    try: 
        cursor = conn.cursor()
        if messageFromSender.from_user.first_name and messageFromSender.from_user.last_name:
            name = messageFromSender.from_user.first_name  + " " + messageFromSender.from_user.last_name
        else: 
            name = "Имя не указано"
        if forwardedMessage.sender_chat:
            chatId = forwardedMessage.sender_chat.id 
        else:
            chatId = forwardedMessage.chat.id
        cursor.execute(
            'INSERT INTO for_posts (user_id, username, created_at, text, message_id, chanel_id) VALUES (?, ?, ?, ?, ?, ?)', 
            (
                messageFromSender.from_user.id,
                name, 
                messageFromSender.date, 
                messageFromSender.text, 
                forwardedMessage.message_id, 
                chatId
            )
        )
        conn.commit()
        cursor.close()
    except sqlite3.Error as error: 
        print("Ошибка сохранения: ", error)
    finally: 
        print("Все прошло успешно")

Сохранение ответа диспетчера на пост (в комментариях):

def save_engineer_reply_info(message): ##Ответ на пост (время ответа)
    cursor = conn.cursor()
    if message.from_user.first_name and message.from_user.last_name:
        name = message.from_user.first_name  + " " + message.from_user.last_name
    else:
        name = "Имя не указано"
        
    if message.sender_chat:
        chatId = message.sender_chat.id 
    else:
        chatId = message.chat.id
    Replymessage = cursor4.execute ('SELECT * FROM for_reply WHERE(reply_to_id) = (?)', (message.reply_to_message.forward_from_message_id,))
    if Replymessage.fetchone() is None:
        cursor.execute(
            'INSERT INTO for_reply (user_id, username, created_at, text, reply_to_id, message_id, chanel_id) VALUES (?, ?, ?, ?, ?, ?,?)', 
            (
                message.from_user.id, 
                name, 
                message.date, 
                message.text, 
                message.reply_to_message.forward_from_message_id, 
                message.message_id, 
                chatId
            )
        )
        conn.commit()
        cursor.close()
    else:
        print('есть в базе')
        cursor.close()
        return

Статистика по пользователям:

def collect_analytics_by_user(message):
    validationResult = check_user_date(message.text)
    cursor5 = conn.cursor()
    query = "SELECT strftime('%d-%m-%Y', datetime(for_posts.created_at, 'unixepoch', 'localtime')), for_reply.user_id, for_reply.created_at - for_posts.created_at  as diff, for_reply.username FROM for_posts LEFT JOIN for_reply ON for_reply.reply_to_id = for_posts.message_id"
    query = query + " WHERE for_reply.created_at BETWEEN ? AND ? GROUP BY for_posts.message_id ORDER BY for_posts.created_at DESC" ##for_posts.created_at , for_reply.created_at (БЫЛА) ##
    ##GROUP BY for_posts.message_id ORDER BY for_posts.created_at DESC" было 

    if validationResult == CheckDateResult.DATE:    
        cursor5.execute(query, dateToTimestamp(message.text, message.text))
    elif validationResult == CheckDateResult.INTERVAL:
        interval = message.text.split("-")
        cursor5.execute(
            query, 
            dateToTimestamp(interval[0], interval[1])
        )    
    else:
        analytics_ask_date(message)
        return 
    result = cursor5.fetchall()
    cursor5.close
    print(result)
    # open the file in the write mode
    with open('report' + str(message.message_id) + ".csv", 'a+', encoding='UTF8', newline='') as f: ##Выгрузка общая по всем каналам 
        # create the csv writer
        writer = csv.writer(f)
        writer.writerow([
            "Интервал", 
            "ФИО",
            "Всего сообщений, шт", 
            "До \n30 сек", 
            "После \n30 секунд", 
            "SL%"
        ])
        interval = message.text
        messagesCount = len(result)
        under30 = 0
        top30 = 0
        byGroup = {}
        
        for row in result: 
            try:
                byGroup[row[1]]["s"] = 0
            except KeyError:
                byGroup[row[1]] = {
                    "top30": 0,
                    "under30": 0,
                    "messagesCount": 0,
                    "s": 0
                }
            if row[2] == None:
                messagesCount = messagesCount - 1
                continue
            if byGroup[row[1]]["messagesCount"] == None:
                    byGroup[row[1]]["messagesCount"] = 0
            byGroup[row[1]]["messagesCount"] += 1

            if row[2] > 30:
                if byGroup[row[1]]["top30"] == None:
                    byGroup[row[1]]["top30"] = 0
                byGroup[row[1]]["top30"] += 1
            else: 
                if byGroup[row[1]]["under30"] == None:
                    byGroup[row[1]]["under30"] = 0
                byGroup[row[1]]["under30"] += 1
                        
        for key in byGroup.keys():
            if byGroup[key]["messagesCount"] == 0:
                continue
            top30 = byGroup[key]["top30"]
            under30 = byGroup[key]["under30"]
            messagesCount = byGroup[key]["messagesCount"]
            percent = messagesCount / 100 #round((int(top30) / (int(messagesCount) / 100)), 2)
            sl = round(under30 / percent, 2)
            try: 
                managerName = managers[key]
            except KeyError:
                continue

            writer.writerow([
                interval, 
                managerName,
                str(messagesCount),
                str(under30),
                str(top30),
                str(sl)
            ])  
        f.close()
    csv_to_execel('report' + str(message.message_id))
    with open('report' + str(message.message_id) + ".xlsx", 'rb') as f: ##сохранение документа и его отправка
        bot.send_document(message.chat.id, f)
        f.close()

Статистика по каналам:

def collect_analytics_by_group(message):
    validationResult = check_user_date(message.text)
    cursor5 = conn.cursor()
    query = "SELECT strftime('%d-%m-%Y', datetime(for_posts.created_at, 'unixepoch', 'localtime')), for_posts.chanel_id, for_reply.created_at - for_posts.created_at  as diff FROM for_posts LEFT JOIN for_reply ON for_reply.reply_to_id = for_posts.message_id"
    query = query + " WHERE for_posts.created_at BETWEEN ? AND ? GROUP BY for_posts.message_id ORDER BY for_posts.created_at DESC" 

    if validationResult == CheckDateResult.DATE:    
        cursor5.execute(query, dateToTimestamp(message.text, message.text))
    elif validationResult == CheckDateResult.INTERVAL:
        interval = message.text.split("-")
        cursor5.execute(
            query, 
            dateToTimestamp(interval[0], interval[1])
        )    
    else:
        analytics_ask_date(message)
        return 
    result = cursor5.fetchall()
    cursor5.close

    # open the file in the write mode
    with open('report' + str(message.message_id) + ".csv", 'a+', encoding='UTF8', newline='') as f: ##Выгрузка общая по всем каналам 
        # create the csv writer
        writer = csv.writer(f)
        writer.writerow([
            "Интервал", 
            "Группа",
            "Всего сообщений, шт", 
            "До \n30 сек", 
            "После \n30 секунд", "SL%"])
        interval = message.text
        messagesCount = len(result)
        under30 = 0
        top30 = 0
        byGroup = {}
        
        for row in result: 
            try:
                byGroup[row[1]]["s"] = 0
            except KeyError:
                byGroup[row[1]] = {
                    "top30": 0,
                    "under30": 0,
                    "messagesCount": 0,
                    "s": 0
                }
            if row[2] == None:
                messagesCount = messagesCount - 1
                continue
            if byGroup[row[1]]["messagesCount"] == None:
                    byGroup[row[1]]["messagesCount"] = 0
            byGroup[row[1]]["messagesCount"] += 1

            if row[2] > 30:
                if byGroup[row[1]]["top30"] == None:
                    byGroup[row[1]]["top30"] = 0
                byGroup[row[1]]["top30"] += 1
            else: 
                if byGroup[row[1]]["under30"] == None:
                    byGroup[row[1]]["under30"] = 0
                byGroup[row[1]]["under30"] += 1
                        
        for key in byGroup.keys():
            if byGroup[key]["messagesCount"] == 0:
                continue
            top30 = byGroup[key]["top30"]
            under30 = byGroup[key]["under30"]
            messagesCount = byGroup[key]["messagesCount"]
            percent = messagesCount / 100 #round((int(top30) / (int(messagesCount) / 100)), 2)
            sl = round(under30 / percent, 2)
            writer.writerow([
                interval, 
                channels[key],
                str(messagesCount),
                str(under30),
                str(top30),
                str(sl)
            ])  
        f.close()
    csv_to_execel('report' + str(message.message_id))
    with open('report' + str(message.message_id) + ".xlsx", 'rb') as f: ##сохранение документа и его отправка
        bot.send_document(message.chat.id, f)
        f.close()

[Алан Гибизов]

Код поместите в вопрос, спрячьте под спойлеры для удобства отвечающих.

[twink132]

Алан Гибизов Dr. Bacon, , срабатывает ограничение по символам (лимит до 10 тысяч символов) в этой связи поместил в комментариях, возможно у Вас есть какой-либо вариант выхода из положения? буду благодарен. В коментариях добавил спойлер

[Алан Гибизов]

twink132, а вы уверены, что нужен прям весь код? Оставить существенное не получается?

[twink132]

Алан Гибизов, я сократил до того что относится к теме моего вопроса, а именно код формирования отчетов по которым вопросы (их на самом деле больше) и каким образом сохраняются в БД посты и ответы на них. Полный исходный код длинною 1800 строчек, поэтому изначально пытался сократить его по максимуму.

[Алан Гибизов]

twink132, ясно. Что ж, будем надеяться, что найдутся отвечающие, кто возьмет на себя труд разобраться в этом.

[fenrir]

Алан Гибизов, по пунктам:
1) Значит ошибка в вычислениях. Судя по описанию время ответа = reated_at - created_at.
2) По логике ключевыми полями должны быть id и channel_id, тогда дублирующиеся id у сообщений не будут проблемой
3) Не поможет, если не разобраться что не так у вас сохраняется.

Код подробно не смотрел. На мой взгляд сами записи в таблицу пишутся неверно в save_engineer_reply_info

Replymessage = cursor4.execute ('SELECT * FROM for_reply WHERE(reply_to_id) = (?)', (message.reply_to_message.forward_from_message_id,))

в WHERE учитывается только id сообщения и игнорируется канал, из-за чего скорее всего дальше по коду не срабатывает INSERT и не все записи пишутся.

Стоит еще раз посмотреть на БД, судя по проблемам у вас там возможно даже нет ключевых полей или они неверно определены иначе проблема дубликатов не должна была возникнуть.
Поправить связь таблиц, убедиться что каждая запись однозначно определяет обращение или ответ, по возможности написать миграцию и перенести часть не дублирующихся данных из старой базы в новую и уже на новой делать аналитику.