BigQuery SQL – انجمن DEV

Summarize this content to 400 words in Persian Lang
در اینجا مجموعه ای از پرس و جوهای پیشرفته SQL، از جمله تکنیک های قدرتمند مانند توابع پنجره، دستکاری رشته، پرس و جوهای بازگشتی، بهینه سازی و نمونه های خاص BigQuery وجود دارد. این مثال ها برای حل مسائل پیچیده در پروژه های واقعی مفید هستند.

1. توابع پنجره

توابع پنجره برای محاسبه تجمعات در زیر مجموعه های داده بدون از دست دادن جزئیات خط به خط عالی هستند.

— Exemplo: Classificar filmes por avaliação (rating) dentro de cada ano de lançamento
SELECT
title,
release_year,
rating,
ROW_NUMBER() OVER (PARTITION BY release_year ORDER BY rating DESC) AS rank
FROM netflix;

— Exemplo: Calcular a média acumulada de avaliações
SELECT
title,
release_year,
rating,
AVG(rating) OVER (PARTITION BY release_year ORDER BY release_year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS media_acumulada
FROM netflix;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

ROW_NUMBER(): به هر خط در یک پارتیشن یک عدد اختصاص می دهد (در این مورد، بر اساس سال انتشار).

AVG() com OVER: میانگین انباشته شده را با توجه به ردیف های قبلی در همان پارتیشن محاسبه می کند.

2. دستکاری رشته

— Exemplo: Dividir nomes dos filmes e extrair a primeira palavra
SELECT
title,
SPLIT(title, ‘ ‘)[OFFSET(0)] AS primeira_palavra
FROM netflix;

— Exemplo: Buscar títulos que começam com “The”
SELECT
title
FROM netflix
WHERE title LIKE ‘The%’;

— Exemplo: Substituir espaços por traços
SELECT
title,
REPLACE(title, ‘ ‘, ‘-‘) AS titulo_formatado
FROM netflix;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

SPLIT(): رشته ها را بر اساس جداکننده تقسیم می کند که برای استخراج قطعات مفید است.

LIKE: به جستجو با الگوها اجازه می دهد.

REPLACE(): جایگزین بخش های خاصی از یک رشته می شود.

3. پرس و جوهای بازگشتی

پرس و جوهای بازگشتی به حل مسائل سلسله مراتبی مانند درختان یا زنجیره ها کمک می کنند.

— Exemplo: Montar uma hierarquia fictícia de categorias de filmes
WITH RECURSIVE categorias AS (
SELECT
id,
nome_categoria,
id_categoria_pai
FROM categorias_filmes
WHERE id_categoria_pai IS NULL — Começa pela raiz

UNION ALL

SELECT
c.id,
c.nome_categoria,
c.id_categoria_pai
FROM categorias_filmes c
INNER JOIN categorias cat ON c.id_categoria_pai = cat.id
)
SELECT * FROM categorias;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

WITH RECURSIVE: پرس و جوی را تعریف می کند که برای کشف ساختارهای سلسله مراتبی به خود ارجاع می دهد.
پرس و جو پایه (WHERE id_categoria_pai IS NULL) ریشه و the را مشخص می کند UNION ALL اجازه می دهد تا زیر مجموعه ها اضافه شود.

4. بهینه سازی با شاخص های CTE و سوالات فرعی

استفاده از ایندکس ها و تقسیم پرس و جوها به مراحل می تواند کارایی را بهبود بخشد.

— Exemplo: Calcular a receita total por gênero usando uma CTE
WITH receita_por_genero AS (
SELECT
genero,
SUM(receita) AS total_receita
FROM filmes
GROUP BY genero
)
SELECT
genero,
total_receita
FROM receita_por_genero
WHERE total_receita > 1000000;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

WITH: یک پرس و جو موقت قابل استفاده مجدد (CTE) ایجاد می کند تا کد را تمیزتر و کارآمدتر کند.
در حال فیلتر کردن total_receita > 1000000 تنها پس از گروه بندی رخ می دهد.

5. پرس و جوهای پیشرفته BigQuery

BigQuery از SQL استاندارد استفاده می کند، اما با ویژگی های خاص برای حجم زیاد داده و تجزیه و تحلیل پیشرفته.

مثال 1: تجزیه و تحلیل Log با آرایه ها و STRUCT ها

— Dataset fictício: Análise de cliques em anúncios
SELECT
user_id,
ARRAY_AGG(STRUCT(ad_id, click_time)) AS cliques
FROM `projeto.dataset.logs_cliques`
GROUP BY user_id;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

ARRAY_AGG: رکوردها را در یک آرایه گروه بندی می کند و چندین کلیک را برای هر کاربر حفظ می کند.

STRUCT: به شما امکان می دهد ستون های مرتبط را در آرایه ذخیره کنید.

مثال 2: تجزیه و تحلیل جدول تقسیم شده

BigQuery با جداول پارتیشن بندی شده به خوبی کار می کند، که عملکرد را در پرس و جوهای خاص بهبود می بخشد.

— Consultar apenas partições recentes (últimos 7 dias)
SELECT
user_id,
COUNT(*) AS total_cliques
FROM `projeto.dataset.logs_cliques`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_id;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

_PARTITIONTIME: ستون ویژه ای که در جداول پارتیشن بندی شده برای شناسایی پارتیشن ها استفاده می شود.
محدود کردن پارتیشن ها هزینه پرس و جو را کاهش می دهد.

مثال 3: توابع تحلیلی با موقعیت جغرافیایی

— Exemplo: Identificar o país mais frequente de acesso para cada usuário
SELECT
user_id,
APPROX_TOP_COUNT(country, 1) AS pais_mais_frequente
FROM `projeto.dataset.logs_acessos`
GROUP BY user_id;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

APPROX_TOP_COUNT: عملکرد بهینه شده برای شناسایی بیشترین مقادیر در مجموعه داده های بزرگ.

مثال 4: کاوش JSON در BigQuery

— Exemplo: Extrair dados estruturados de uma coluna JSON
SELECT
JSON_VALUE(payload, ‘$.user.id’) AS user_id,
JSON_VALUE(payload, ‘$.user.name’) AS user_name
FROM `projeto.dataset.logs_api`;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

JSON_VALUE: مقادیر را از یک رشته JSON بر اساس یک مسیر مشخص استخراج می کند.
ایده آل برای لاگ های API یا داده های نیمه ساختار یافته.

6. تاریخ و زمان نمایش داده شد

— Exemplo: Agrupar por dia da semana
SELECT
EXTRACT(DAYOFWEEK FROM data_acesso) AS dia_semana,
COUNT(*) AS total_acessos
FROM acessos
GROUP BY dia_semana
ORDER BY total_acessos DESC;

— Exemplo: Encontrar a diferença em dias entre duas datas
SELECT
user_id,
DATE_DIFF(data_fim, data_inicio, DAY) AS dias_diferenca
FROM acessos;

— Exemplo: Criar intervalos de tempo customizados
SELECT
TIMESTAMP_TRUNC(data_acesso, HOUR) AS hora,
COUNT(*) AS acessos_por_hora
FROM acessos
GROUP BY hora
ORDER BY hora;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

توضیح:

توابع تاریخ/زمان مانند EXTRACT، DATE_DIFF ه TIMESTAMP_TRUNC کمک به تجزیه و تحلیل روندهای زمانی و ایجاد گزارش.

😊

-- Exemplo: Classificar filmes por avaliação (rating) dentro de cada ano de lançamento
SELECT 
    title,
    release_year,
    rating,
    ROW_NUMBER() OVER (PARTITION BY release_year ORDER BY rating DESC) AS rank
FROM netflix;

-- Exemplo: Calcular a média acumulada de avaliações
SELECT 
    title,
    release_year,
    rating,
    AVG(rating) OVER (PARTITION BY release_year ORDER BY release_year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS media_acumulada
FROM netflix;

-- Exemplo: Dividir nomes dos filmes e extrair a primeira palavra
SELECT 
    title,
    SPLIT(title, ' ')[OFFSET(0)] AS primeira_palavra
FROM netflix;

-- Exemplo: Buscar títulos que começam com "The"
SELECT 
    title
FROM netflix
WHERE title LIKE 'The%';

-- Exemplo: Substituir espaços por traços
SELECT 
    title,
    REPLACE(title, ' ', '-') AS titulo_formatado
FROM netflix;

-- Exemplo: Montar uma hierarquia fictícia de categorias de filmes
WITH RECURSIVE categorias AS (
    SELECT 
        id,
        nome_categoria,
        id_categoria_pai
    FROM categorias_filmes
    WHERE id_categoria_pai IS NULL -- Começa pela raiz

    UNION ALL

    SELECT 
        c.id,
        c.nome_categoria,
        c.id_categoria_pai
    FROM categorias_filmes c
    INNER JOIN categorias cat ON c.id_categoria_pai = cat.id
)
SELECT * FROM categorias;

-- Exemplo: Calcular a receita total por gênero usando uma CTE
WITH receita_por_genero AS (
    SELECT 
        genero,
        SUM(receita) AS total_receita
    FROM filmes
    GROUP BY genero
)
SELECT 
    genero,
    total_receita
FROM receita_por_genero
WHERE total_receita > 1000000;

-- Dataset fictício: Análise de cliques em anúncios
SELECT
    user_id,
    ARRAY_AGG(STRUCT(ad_id, click_time)) AS cliques
FROM `projeto.dataset.logs_cliques`
GROUP BY user_id;

-- Consultar apenas partições recentes (últimos 7 dias)
SELECT
    user_id,
    COUNT(*) AS total_cliques
FROM `projeto.dataset.logs_cliques`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_id;

-- Exemplo: Identificar o país mais frequente de acesso para cada usuário
SELECT
    user_id,
    APPROX_TOP_COUNT(country, 1) AS pais_mais_frequente
FROM `projeto.dataset.logs_acessos`
GROUP BY user_id;

-- Exemplo: Extrair dados estruturados de uma coluna JSON
SELECT
    JSON_VALUE(payload, '$.user.id') AS user_id,
    JSON_VALUE(payload, '$.user.name') AS user_name
FROM `projeto.dataset.logs_api`;

-- Exemplo: Agrupar por dia da semana
SELECT
    EXTRACT(DAYOFWEEK FROM data_acesso) AS dia_semana,
    COUNT(*) AS total_acessos
FROM acessos
GROUP BY dia_semana
ORDER BY total_acessos DESC;

-- Exemplo: Encontrar a diferença em dias entre duas datas
SELECT
    user_id,
    DATE_DIFF(data_fim, data_inicio, DAY) AS dias_diferenca
FROM acessos;

-- Exemplo: Criar intervalos de tempo customizados
SELECT
    TIMESTAMP_TRUNC(data_acesso, HOUR) AS hora,
    COUNT(*) AS acessos_por_hora
FROM acessos
GROUP BY hora
ORDER BY hora;