Perguntas[window-functions](dba)

Estou procurando ajuda para obter uma soma contínua da diferença entre duas datas que estão em linhas separadas.

A tabela se parece com:

Eu tenho uma consulta que analisa a data_final anterior e, se estiver dentro de um dia, esse é um contrato contínuo.

SELECT
    case when
        (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
         else false
    end as continous_contract,
    end_date - start_date as contract_days,
    client_id,
    contract_id,
    autopay_status,
    start_date,
    end_date
FROM
    client_contracts
ORDER BY
    client_id, start_date

Isso adiciona duas colunas de alias extras.

Meu objetivo aqui é resumir os dias em que um cliente teve um contrato contínuo para que a tabela seja semelhante à abaixo para o exemplo acima:

Eu coloquei a consulta abaixo, mas ela resume apenas os dois valores anteriores.

SELECT
*
FROM
(
    SELECT
        *,
        case
            when cc.continuous_contract = true then
                cc.contract_days + coalesce(lag(cc.contract_days) over (partition by cc.client_id), 1)
            else cc.contract_days
        end as added_contract_days
    FROM (
        SELECT
            case when
                (start_date - coalesce(lag(end_date) over (partition by client_id order by end_date), end_date)::date)::int <= 1 then true
                 else false
            end as continuous_contract,
            end_date - start_date as contract_days,
            client_id,
            contract_id,
            autopay_status,
            start_date,
            end_date
        FROM
            client_contracts
        ) as cc
    ) as ccc

Estou feliz em mudar qualquer coisa para fazer este trabalho.

Fiddle para auxiliar na visualização de dados e estrutura: Fiddle

Eu tenho essa tabela:

Eu quero criar uma nova coluna chamada soldque é baseada em um cálculo - que será uma soma em execução harvestedem um período de meses (set-jun). Todo mês de setembro, soldsempre será 1 (ou 100 em por cento). O cálculo para outubro de 2021 será fruits/ ( harvested+ harvested_Nov) = 143 / 11 + 139.

Para os restantes meses de 2021, segue o mesmo formato: fruits/ ( harvested+ harvested_until_Sep) --> esta será uma soma corrente, a partir do mês em que se encontra, e termina em setembro do ano anterior.

Outro exemplo para 2022 é o cálculo para março de 2022 = fruits/ ( harvested+ harvested_Feb_2022+ harvested_Jan_2022+ harvested_Dec_2021+ harvested_Nov_2021+ harvested_Oct_2021+ harvested_Sep_2021) = 123 / (5+12+10+9+14+11+139).

A tabela deve ficar assim:

Eu tentei isso:

select 
    month_rep,
    fruits,
    harvested,
    case when extract(month from "month_rep") in (7, 8) then null
         when extract(month from "month_rep") = 9 then 1
        else ROUND(fruits / sum(harvested) over (order by month_rep), 2) end sold
from my_table 

Isso funciona bem, mas apenas quando tenho dados antes de setembro de 2022. Eu quero que julho e agosto tenham null sold- o que funciona. Após agosto, setembro de 2022 deve ser um novo período onde soldé 1. Depois disso, outubro de 2022 será calculado como fruits/ ( harvested+ harvested_Sep_2022) - onde iniciamos um novo período para o 2º período de setembro de 2022 a junho de 2023.

Existe uma maneira de agrupar esses "períodos" e ter a soma acumulada sobre isso? Talvez eu precise encontrar uma maneira de agrupar o período e particionar a partir disso.

Eu tenho um banco de dados SQL Server com uma transactionstabela com client_id, datee is_cancelled.

Estou tentando obter os client_ids que têm 3 ou mais transações em uma linha marcadas como is_cancelled, junto com a contagem in_a_row. Cheguei ao seguinte, o que me dá um 1 para is_same quando os suportes is_cancelled são consecutivos e um total de transações canceladas (que não é bem o que eu preciso)

SELECT 
client_id,
date,
is_same,
SUM(is_same) OVER (PARTITION BY client_id ORDER BY date) AS sum_same,
transCancelled
FROM
(
    SELECT
    client_id,
    LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
    CASE 
        WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN 1
        ELSE 0
    END as is_same,
    date,
    is_cancelled
    FROM transactions
    WHERE deleted_at IS NULL -- Ignore soft-deleted rows
) AS t_01
WHERE previous_cancelled = 1
ORDER BY date

Mexa com dados de amostra: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a0c9b12203ab2d0c83f73604ccc9d0a0

Dados esperados (client_id, contagem) 1, 3 3, 6

estou usando mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64).

Estou tendo uma referência de tabela:

CREATE TABLE `referrals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `referred_by` bigint(20) unsigned DEFAULT NULL,
  `referral_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `referrals_email_unique` (`email`)
);

Na tabela você basicamente insere a pessoa e quem a encaminhou.

Criei a seguinte tabela de classificação:

select
        referred_by,
        count(referred_by) as referred_by_count,
            @curRank := @curRank + 1 AS rank
from
        referrals r,
    (
    SELECT
        @curRank := 0) o
group by
        referred_by
order by
        referred_by_count DESC;

Isso me dá:

No entanto, gostaria de obter:

Ver no DB Fiddle

Eu criei o seguinte dbfiddle usando o plugin faker php.

Eu tentei usar a RANK()função mysql. No entanto, estou lutando como usá-lo corretamente.

Eu aprecio suas respostas!

Preciso responder a seguinte pergunta:

Qual é o número médio de envios de e-mail que leva antes que um cliente abra um e-mail? Eu sei que preciso contar o número de linhas entre cada Open_Date para obter o número de envios de email entre cada abertura. Percebo que preciso adicionar a função row_number () para isso, mas não consigo chegar à coluna desejada na minha segunda captura de tela.

Algo a ter em mente, precisarei obter um "CounttilOpens" médio por endereço de e-mail. No meu exemplo abaixo, tenho dois endereços de e-mail diferentes e suas respectivas datas de envio e abertura e preciso que a consulta funcione de forma independente para cada endereço de e-mail.

Meus dados estão estruturados da seguinte forma:

Preciso de uma consulta que me ajude a obter uma nova coluna como esta que reinicia para cada endereço de e-mail e para cada intervalo de datas abertas:

Quero identificar clientes que gastaram mais de D dólares em um período de P horas. Suponha que D=$10 e P=48. Em seguida, nos dados da transação abaixo:

CUSTOMER         STAMP        AMOUNT
--------         -----        ------
   A     2021-06-02 00:22:53    1.44
   A     2021-06-02 06:24:17    1.51
   A     2021-06-03 07:09:45    2.73
   A     2021-06-03 15:57:30    3.92
   A     2021-06-04 06:41:21    1.83
   B     2021-06-01 02:50:22    2.65
   B     2021-06-03 07:01:36    4.05
   B     2021-06-04 05:20:10    3.30
   B     2021-06-04 09:53:53    2.64
   B     2021-06-04 14:54:00    2.26
   C     2021-06-01 16:01:38    2.61
   C     2021-06-01 23:38:25    1.16
   C     2021-06-02 14:41:02    2.82
   C     2021-06-03 00:28:37    1.54
   C     2021-06-03 02:06:46    1.19
   C     2021-06-04 17:16:29    2.05

o cliente B atende aos critérios e os clientes A e C não (mesmo que seus gastos totais > $ 10).

Eu sei como usar funções de janelas, mas neste caso não sei de antemão o tamanho da janela.

Acho que a resposta seria algo assim:

select
  customer
, stamp
, amount
, amount > coalesce(sum(amount) over (order by extract('epoch_second', stamp)
range between 2*24*60*60 preceding and current row), 0) as is_alert
from t
where amount is not null
order by stamp

mas meu banco de dados é Snowflake e não parece oferecer suporte a essa sintaxe . Essa página diz:

Para frames de janela cumulativos: ... RANGE é semelhante a ROWS, exceto que apenas calcula o resultado para linhas que têm o mesmo valor que a linha atual (de acordo com a subcláusula ORDER BY especificada).

Eu tenho uma coluna TIMESTAMP:

dates
2021-06-24 05:47:05
2021-06-24 09:47:05
2021-06-24 13:47:05
2021-06-24 17:47:05

Quero escolher o primeiro carimbo de data/hora de um determinado dia 3 horas ou mais antes do próximo carimbo de data/hora desse mesmo dia.

expected output:

2021-06-24 05:47:05

No entanto , se não houver um carimbo de data/hora mais de 3 horas antes de qualquer outro (nesse dia), o último carimbo de data/hora desse dia deverá ser retornado.

WITH trips_by_day AS
                      (
                      SELECT DATE(trip_start_timestamp) AS trip_date,
                          COUNT(*) as num_trips
                      FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                      WHERE trip_start_timestamp >= '2016-01-01' AND trip_start_timestamp < '2018-01-01'
                      GROUP BY trip_date
                      ORDER BY trip_date
                      )
                      SELECT trip_date,
                          avg(num_trips)
                          OVER (
                               order by trip_date
                               rows between 15 preceding and 15 following
                               ) AS avg_num_trips
                      FROM trips_by_day

Alguém poderia me explicar o significado de rows between 15 preceding and 15 following?

Eu tenho uma tabela como abaixo onde as ações do usuário são armazenadas com um carimbo de data/hora. Meu objetivo é identificar a ação que aconteceu antes de uma ação específica (chamada reference_action) e contar o número dessas ações para ver quais ações acontecem antes das ações específicas e como elas são distribuídas.

Estou ciente de funções de janela como LAG() onde posso obter a linha antes de uma determinada linha, mas não consigo descobrir como incluir uma restrição como WHERE action_name = "reference_action".

O mecanismo de consulta é Presto e as tabelas são tabelas Hive, mas estou mais interessado na abordagem SQL geral, portanto, isso não deve importar muito.

O resultado deve se parecer com:

Existem duas linhas em que "desired_action1" é seguido diretamente por uma linha com "reference_action", quando ordenado por timestamp, portanto, a contagem é 2. A mesma lógica se aplica ao motivo pelo qual a contagem é 1 para "desired_action2".

O objetivo é saber o que um usuário fez antes de fazer uma compra (compra = reference_action). Para entender o que ele fez antes, quero pesquisar a ação que aconteceu antes de uma compra. Portanto, preciso saber o action_name na linha antes de um reference_action. as ações_desejadas precisam ser contadas, as ações_referência são apenas as linhas após as ações que eu quero contar e usadas para determinar quais valores devem ser contados.

Eu tenho uma visão que calcula a soma contínua tomando o valor da linha anterior com base na PARTITION BYcláusula e preciso indicar que um campo na PARTITION BYcláusula pode ter nulos.

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Às vezes SupplierFacilityIdé nulo e tudo bem, ainda deve ser incluído no sum()cálculo.

Isso é possível com o Postgres?