问题[window-functions](dba)

我正在寻求帮助，以获得位于不同行的两个日期之间差异的运行总和。

表看起来像：

我有一个查询，查看前一个 end_date，如果它在一天之内，那么这是一份持续合同。

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

这增加了两个额外的别名列。

我的目标是总结客户拥有连续合同的天数，因此上述示例的表格如下所示：

我将下面的查询放在一起，但它只总结了前两个值。

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

我很高兴改变任何事情来完成这项工作。

Fiddle 用于协助查看数据和结构： Fiddle

我有这张桌子：

我想创建一个名为sold基于计算的新列 - 这将是harvested几个月（9 月至 6 月）内的运行总和。每年 9 月，sold将始终为 1（或 100%）。2021 年 10 月的计算将是fruits/ ( harvested+ harvested_Nov) = 143 / 11 + 139。

对于 2021 年的其余月份，遵循相同的格式：fruits/ ( harvested+ harvested_until_Sep) --> 这将是一个运行总和，从您所在的月份开始，到上一年的 9 月结束。

2022 年的另一个示例是 2022 年 3 月的计算 = 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)。

该表应如下所示：

我试过这个：

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 

这很好用，但只有当我有 2022 年 9 月之前的数据时。我希望 Jul 和 Aug 为 null sold- 这很有效。2022 年 8 月之后，2022 年 9 月应该是一个新的时期，其中sold1。在那之后，2022 年 10 月将计算为fruits/ ( harvested+ harvested_Sep_2022) - 我们从 2022 年 9 月 2 日到 2023 年 6 月的第二个时期开始一个新时期。

有没有办法对这些“时期”进行分组并对其进行汇总？我可能需要找到一种方法来对期间进行分组并从中进行分区。

我有一个带有 、 和 的表的transactionsSQL Server 数据库。client_iddateis_cancelled

我正在尝试获取在标记为 is_cancelled的行中有 3 个或更多事务的 client_ids ，以及 in_a_row 计数。我已经得到了以下内容，当 is_cancelled 支持是连续的时，is_same 的值为 1，并且取消的事务总数为 1（这不是我所需要的）

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

摆弄示例数据：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a0c9b12203ab2d0c83f73604ccc9d0a0

预期数据（client_id，count） 1, 3 3, 6

我正在使用mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64).

我有一个表推荐：

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`)
);

在表格中，您基本上插入了人和推荐人。

我创建了以下排行榜：

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;

这给了我：

但是，我想得到：

在 DB Fiddle 上查看

我使用 faker php 插件创建了以下dbfiddle 。

我尝试使用 mysqlRANK()功能。但是，我正在努力如何正确使用它。

感谢您的回复！

我需要回答以下问题：

在客户打开电子邮件之前，平均发送电子邮件的次数是多少？我知道我需要计算每个 Open_Date 之间的行数，以便获得每次打开之间发送的电子邮件数量。我意识到我需要为此添加 row_number () 函数，但我似乎无法在第二个屏幕截图中找到所需的列。

需要记住的一点是，我需要每个电子邮件地址平均获得一个“CounttilOpens”。在下面的示例中，我有两个不同的电子邮件地址以及它们各自的发送和打开日期，并且我需要查询来为每个电子邮件地址独立运行。

我的数据结构如下：

我需要一个查询来帮助我获得一个像这样的新列，该列会针对每个电子邮件地址和每个开放日期间隔重新启动：

我想找出在 P 小时内花费超过 D 美元的客户。假设 D=10 美元，P=48。然后在下面的交易数据中：

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

客户 B 符合标准，客户 A 和 C 不符合（即使他们的总支出 > 10 美元）。

我知道如何使用窗口函数，但在这种情况下，我事先不知道窗口应该有多大。

我认为答案是这样的：

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

但我的数据库是雪花，它似乎不支持这种语法。该页面说：

对于累积窗口框架： ... RANGE 类似于 ROWS，除了它只计算与当前行具有相同值的行的结果（根据指定的 ORDER BY 子句）。

我有一个 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

我想选择在同一天的下一个时间戳之前 3 小时或更长时间的给定日期的第一个时间戳。

expected output:

2021-06-24 05:47:05

但是，如果没有时间戳比任何其他时间戳（在给定日期）早 3 小时以上，则应返回当天的最后一个时间戳。

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

谁能给我解释一下的意思rows between 15 preceding and 15 following？

我有一个如下表，其中用户操作与时间戳一起存储。我的目标是识别在特定操作（名为reference_action）之前发生的操作并计算这些操作的数量以查看哪些操作在特定操作之前发生以及它们是如何分布的。

我知道像 LAG() 这样的窗口函数，我可以在其中获取某一行之前的行，但不知道如何包含像WHERE action_name = "reference_action".

查询引擎是 Presto，表是 Hive 表，但我最感兴趣的是一般的 SQL 方法，因此这无关紧要。

结果应如下所示：

有两行，其中“desired_action1”直接跟在“reference_action”行之后，当按 排序时timestamp，因此计数为 2。同样的逻辑适用于为什么“desired_action2”的计数为 1。

目标是了解用户在购买之前做了什么（购买 = reference_action）。要了解他之前做了什么，我想查找购买之前发生的操作。因此，我需要在reference_action 之前知道行中的action_name。required_actions 必须被计算，reference_actions 只是我想要计算的操作之后的行，用于确定应该计算哪些值。

我有一个视图，它根据PARTITION BY子句计算前一行的值的滚动总和，我需要指出PARTITION BY子句中的一个字段可以有空值。

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"

有时SupplierFacilityId为空，这很好，它仍应包含在sum()计算中。

Postgres可以做到这一点吗？