Графики

-- Enum значения для action_role:
"assistant"     -- Действие ассистента
"user"          -- Действие пользователя  
"system"        -- Системное сообщение (инструкции, данные, завершения)
"tool_call"     -- Вызов инструмента агентом
"tool_response" -- Ответ инструмента

-- Enum значения для action_type:
"action"        -- Вызовы инструментов и их ответы
"data"          -- Входящие данные
"error"         -- Ошибки во время разговора
"history"       -- Принудительно добавленные в историю сообщения
"instruction"   -- Инструкция агенту
"line"          -- Реплика в диалоге (от агента или пользователя)
"termination"   -- Завершение диалога

{
  "tool": "tool_name",
  "args": {
    "some_argument": "Значение аргумента"
  }
}

{
    "tool": "tool_name", 
    "response": {
        "output": "Ответ функции", 
        "action": "continue|terminate|transfer",
        "target": "Цель перевода, если action = transfer"
        }
}

SELECT 
    DATE_TRUNC('hour', action_time) AS hour,
    COUNT(DISTINCT interaction_id) AS dialogue_count
FROM dialogues
WHERE 
    agent_id = 147 
    AND interaction_date = '2025-04-30' 
    AND interaction_type = 'dialogue'
GROUP BY hour 
ORDER BY hour;

WITH dialogue_durations AS (
    SELECT 
        interaction_id,
        toUInt64(MAX(action_time) - MIN(action_time)) AS duration_seconds,
        interaction_date
    FROM dialogues
    WHERE 
        agent_id = 147
        AND interaction_type = 'dialogue'
        AND interaction_date >= today() - INTERVAL 7 DAY
    GROUP BY interaction_id, interaction_date
)
SELECT 
    interaction_date,
    ROUND(AVG(duration_seconds), 1) AS average_duration_seconds
FROM dialogue_durations
GROUP BY interaction_date
ORDER BY interaction_date;

WITH interaction_transfer_status AS (
    SELECT 
        interaction_date,
        interaction_id,
        MAX(CASE WHEN action_content LIKE '%"action": "transfer"%' THEN 1 ELSE 0 END) AS has_transfer
    FROM dialogues
    WHERE 
        agent_id = 147
        AND interaction_date >= today() - 7
    GROUP BY interaction_date, interaction_id
)
SELECT 
    interaction_date,
    COUNT(DISTINCT CASE WHEN has_transfer = 0 THEN interaction_id END) AS dialogs_without_transfer,
    COUNT(DISTINCT CASE WHEN has_transfer = 1 THEN interaction_id END) AS dialogs_with_transfer
FROM interaction_transfer_status
GROUP BY interaction_date
ORDER BY interaction_date;

WITH interaction_tool_usage AS (
    SELECT 
        interaction_date,
        interaction_id,
        CASE WHEN EXISTS (
            SELECT 1 
            FROM dialogues AS sub
            WHERE sub.interaction_id = actions.interaction_id
            AND sub.tool_code_name IS NOT NULL
            AND sub.tool_code_name != 'None'
        ) THEN 1 ELSE 0 END AS has_tool_calls
    FROM dialogues
    WHERE interaction_date >= today() - INTERVAL 7 DAY
    GROUP BY interaction_date, interaction_id
)
SELECT 
    interaction_date,
    COUNT(*) AS total_dialogs,
    COUNT(*) - SUM(has_tool_calls) AS dialogs_without_tools,
    ROUND(((COUNT(*) - SUM(has_tool_calls)) * 100.0) / COUNT(*), 2) AS percentage_without_tools
FROM interaction_tool_usage
GROUP BY interaction_date
ORDER BY interaction_date;

SELECT 
    interaction_date,
    AVG(CAST(JSONExtractString(action_content, 'arguments', 'nps_mark') AS Int)) AS average_nps_mark
FROM dialogues
WHERE 
    tool_code_name = 'result_of_nps_survey'
    AND JSONExtractString(action_content, 'arguments', 'nps_mark') != ''
GROUP BY interaction_date
ORDER BY interaction_date;

-- Для извлечения вложенных значений:
JSONExtractString(action_content, 'arguments', 'parameter_name')

-- Проверка типа действия:
action_content LIKE '%"action": "transfer"%'