Contar cuántas veces se ha dicho una palabra

Datos e informes
1

He estado interesado en revisar las tendencias de uso de palabras específicas en mi foro, ¿hay alguna consulta que pueda usar para ver cuántas veces se ha mencionado una subcadena por semana?

2

Encontré uno que funciona con la ayuda de ChatGPT

WITH date_series AS (
  SELECT generate_series(
           DATE_TRUNC('week', MIN(created_at)),  -- Inicio de la primera semana
           DATE_TRUNC('week', MAX(created_at)),  -- Inicio de la última semana
           '1 week'::interval                    -- Intervalo semanal
         ) AS week_start
  FROM posts
),
posts_with_substring AS (
  SELECT
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS total_posts,
    SUM((LENGTH(raw) - LENGTH(REPLACE(lower(raw), lower('your_substring'), ''))) / LENGTH('your_substring')) AS substring_count
  FROM
    posts
  WHERE
    raw ILIKE '%test%'
  GROUP BY
    DATE_TRUNC('week', created_at)
)
SELECT
  ds.week_start,
  COALESCE(pws.total_posts, 0) AS total_posts
FROM
  date_series ds
LEFT JOIN
  posts_with_substring pws ON ds.week_start = pws.week_start
ORDER BY
  ds.week_start
3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.