程序员经验分享-hwhale

每日查询计数，并具有多周的日期限制

2023-12-14

我正在尝试每天查找 # 个活跃用户。

用户在创建后即处于活动状态多于每周 10 个请求，共 4 个连续几周.

IE。 2014 年 10 月 31 日，如果用户在以下时间段内每周发出的请求总数超过 10 个，则该用户处于活动状态：

  1. 2014年10月24日至10月30日AND
  2. 2014年10月17日-10月23日AND
  3. 2014年10月10日-10月16日AND
  4. 2014年10月3日-10月9日

我有一张桌子requests:

CREATE TABLE requests (
  id text PRIMARY KEY, -- id of the request
  amount bigint,       -- sum of requests made by accounts_id to recipient_id,
                       -- aggregated on a daily basis based on "date"
  accounts_id text,    -- id of the user
  recipient_id text,   -- id of the recipient
  date timestamp       -- date that the request was made in YYYY-MM-DD
);

样本值：

INSERT INTO requests2
VALUES
    ('1',  19, 'a1', 'b1', '2014-10-05 00:00:00'),
    ('2',  19, 'a2', 'b2', '2014-10-06 00:00:00'),
    ('3',  85, 'a3', 'b3', '2014-10-07 00:00:00'),
    ('4',  11, 'a1', 'b4', '2014-10-13 00:00:00'),
    ('5',  2,  'a2', 'b5', '2014-10-14 00:00:00'),
    ('6',  50, 'a3', 'b5', '2014-10-15 00:00:00'),
    ('7',  787323, 'a1', 'b6', '2014-10-17 00:00:00'),
    ('8',  33, 'a2', 'b8', '2014-10-18 00:00:00'),
    ('9',  14, 'a3', 'b9', '2014-10-19 00:00:00'),
    ('10', 11, 'a4', 'b10', '2014-10-19 00:00:00'),
    ('11', 1628, 'a1', 'b11', '2014-10-25 00:00:00'),
    ('13', 101, 'a2', 'b11', '2014-10-25 00:00:00');

输出示例：

Date       | # Active users
-----------+---------------
10-01-2014 | 600
10-02-2014 | 703
10-03-2014 | 891

以下是我尝试查找特定日期（例如 2014 年 1 月 10 日）的活跃用户数量的方法：

SELECT count(*)
FROM
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '2 weeks' AND '2014-10-01'::date - interval '1 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_1
JOIN
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '3 weeks' AND '2014-10-01'::date - interval '2 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_2 ON week_1.accounts_id = week_2.accounts_id
JOIN
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '4 weeks' AND '2014-10-01'::date - interval '3 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_3 ON week_2.accounts_id = week_3.accounts_id
JOIN
  (SELECT accounts_id
   FROM requests
   WHERE "date" BETWEEN '2014-10-01'::date - interval '5 weeks' AND '2014-10-01'::date - interval '4 week'
   GROUP BY accounts_id HAVING sum(amount) > 10) week_4 ON week_3.accounts_id = week_4.accounts_id

由于这只是获取 1 天号码的查询，因此我需要随着时间的推移每天获取该号码。我认为这个想法是进行连接来获取日期，所以我尝试做这样的事情：

SELECT week_1."Date_series",
       count(*)
FROM
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '2 weeks' AND requests.date::date - interval '1 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_1
JOIN
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '3 weeks' AND requests.date::date - interval '2 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_2 ON week_1.accounts_id = week_2.accounts_id
AND week_1."Date_series" = week_2."Date_series"
JOIN
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '4 weeks' AND requests.date::date - interval '3 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_3 ON week_2.accounts_id = week_3.accounts_id
AND week_2."Date_series" = week_3."Date_series"
JOIN
  (SELECT to_char(DAY::date, 'YYYY-MM-DD') AS "Date_series",
          accounts_id
   FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 day') DAY, requests
   WHERE to_char(DAY::date, 'YYYY-MM-DD')::date BETWEEN requests.date::date - interval '5 weeks' AND requests.date::date - interval '4 week'
   GROUP BY "Date_series",
            accounts_id HAVING sum(amount) > 10) week_4 ON week_3.accounts_id = week_4.accounts_id
AND week_3."Date_series" = week_4."Date_series"
GROUP BY week_1."Date_series"

然而，我认为我没有得到正确的答案，我也不知道为什么。非常感谢任何提示/指导/指示！ :) :)

附言。我正在使用 Postgres 9.3


这是一个很长的答案，如何使您的查询简短。 :)

Table

在我的表上构建（在您提供不同的表定义之前（odd!) 数据类型：

CREATE TABLE requests (
   id           int
 , accounts_id  int  -- (id of the user)
 , recipient_id int  -- (id of the recipient)
 , date         date -- (date that the request was made in YYYY-MM-DD)
 , amount       int  -- (# of requests by accounts_id for the day)
);

指定日期的活跃用户

“活跃用户”列表某一天:

SELECT accounts_id
FROM  (
   SELECT w.w, r.accounts_id
   FROM  (
      SELECT w
           , day - 6 - 7 * w AS w_start
           , day     - 7 * w AS w_end   
      FROM  (SELECT '2014-10-31'::date - 1 AS day) d  -- effective date here
           , generate_series(0,3) w
      ) w
   JOIN   requests r ON r."date" BETWEEN w_start AND w_end
   GROUP  BY w.w, r.accounts_id
   HAVING sum(r.amount) > 10
   ) sub
GROUP  BY 1
HAVING count(*) = 4;

Step 1

在最里面子查询w（对于“周”）从 4 周的兴趣中建立界限CROSS JOIN给定日期 - 1 的输出为generate_series(0-3).

添加/减去天数date（不是来自时间戳！）只需加/减integer数字。表达方式day - 7 * w从给定日期减去 0-3 乘以 7 天，得出end每周的日期（w_end).
分别减去 6 天（而不是 7 天！）来计算各自的start (w_start).
另外，保留周数w(0-3)用于以后的聚合。

Step 2

In 子查询sub连接来自的行requests到 4 周的集合，其中日期位于开始日期和结束日期之间。GROUP BY周数w和accounts_id.
只有请求总数超过 10 的周才有资格。

Step 3

In the outer SELECT计算每个用户的周数（accounts_id） 合格的。必须为 4 才有资格成为“活跃用户”

每日活跃用户数

This is dynamite.
封装在一个简单的 SQL 函数中以简化一般使用，但查询也可以单独使用：

CREATE FUNCTION f_active_users (_now date = now()::date, _days int = 3)
  RETURNS TABLE (day date, users int) AS
$func$
WITH r AS (
   SELECT accounts_id, date, sum(amount)::int AS amount
   FROM   requests
   WHERE  date BETWEEN _now - (27 + _days) AND _now - 1
   GROUP  BY accounts_id, date
   )
SELECT date + 1, count(w_ct = 4 OR NULL)::int
FROM  (
   SELECT accounts_id, date
        , count(w_amount > 10 OR NULL)
                         OVER (PARTITION BY accounts_id, dow ORDER BY date DESC
                         ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS w_ct
   FROM  (
      SELECT accounts_id, date, dow   
           , sum(amount) OVER (PARTITION BY accounts_id ORDER BY date DESC
                         ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS w_amount
      FROM  (SELECT _now - i AS date, i%7 AS dow
             FROM   generate_series(1, 27 + _days) i) d -- period of interest
      CROSS  JOIN (
             SELECT accounts_id FROM r
             GROUP  BY 1
             HAVING count(*) > 3 AND sum(amount) > 39  -- enough rows & requests
             AND    max(date) > min(date) + 15) a      -- can cover 4 weeks
      LEFT   JOIN r USING (accounts_id, date)
      ) sub1
   WHERE date > _now - (22 + _days)  -- cut off 6 trailing days now - useful?
   ) sub2
GROUP  BY date
ORDER  BY date DESC
LIMIT  _days
$func$ LANGUAGE sql STABLE;

该函数需要任意一天（_now)，默认为“今天”，以及天数 (_days) 结果中，默认为 3。称呼：

SELECT * FROM f_active_users('2014-10-31', 5);

或者不带参数使用默认值：

SELECT * FROM f_active_users();

方法是与第一个查询不同.

SQL小提琴包含表定义的查询和变体。

Step 0

在 CTE 中r每个预聚合金额(accounts_id, date)仅适用于感兴趣的时期，以获得更好的性能。该表仅被扫描once，建议的索引（见下文）将在此处生效。

Step 1

在内部子查询中d生成必要的天数列表：27 + _days行，其中_days是输出中所需的行数，有效为 28 天或更长。
在此过程中，计算一周中的哪一天（dow) 用于步骤 3 中的聚合。i%7与每周间隔一致，查询适用于any不过，间隔。

在内部子查询中a生成唯一的用户列表（accounts_id) 存在于 CTE 中r并通过一些初步的表面测试（足够的行跨越足够的时间和足够的总请求）。

Step 2

生成笛卡尔积d and a with a CROSS JOIN具有每个相关用户的每个相关日期一行. LEFT JOIN to r附加请求数量（如果有）。不WHERE在这种情况下，我们希望每天都有结果，即使根本没有活跃用户。

计算过去一周的总金额（w_amount）在同一步骤中使用窗口函数具有定制框架.例子：

  • 如何在窗口函数中使用环形数据结构

Step 3

现在切断最后6天；这是optional并且可能会或可能不会帮助性能。测试一下：WHERE date >= _now - (21 + _days)

计算满足最低金额的周数（w_ct）在类似的窗口函数中，这次划分为dow另外，框架中仅包含过去 4 周的相同工作日（包含过去一周的总和）。 表达方式count(w_amount > 10 OR NULL)仅计算请求数超过 10 的行。详细解释：

  • 在同一 SELECT sql 查询中从 SUM() 计算百分比

Step 4

在外层SELECT通过...分组date并对过去 4 周的用户进行计数（count(w_ct = 4 OR NULL)）。日期加 1 以补偿偏差 1，ORDER and LIMIT到要求的天数。

业绩与展望

这两个查询的完美索引是：

CREATE INDEX foo ON requests (date, accounts_id, amount);

性能应该不错，但是使用即将推出的 Postgres 会更好9.4，由于新的移动聚合支持：

移动聚合支持在 Postgres Wiki 中。
9.4 手册中的移动聚合

旁白：不要打电话timestamp“日期”列，它是timestamp, not a date。更好的是，永远不要使用基本类型名称，例如date or timestamp作为标识符。曾经。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

postgresql

date

aggregatefunctions

postgresql93

每日查询计数，并具有多周的日期限制 的相关文章

  • Python日期字符串到日期对象

    如何在 python 中将字符串转换为日期对象 该字符串将是 24052010 对应格式 d m Y 我不需要 datetime datetime 对象 而是 datetime date 您可以使用strptime http docs py

  • 使用 .NET 中的类型化数据集将 SQL 参数传递给 IN() 子句

    首先道歉 因为该网站上有类似的问题 但没有一个直接回答这个问题 我在 VS 2010 中使用类型化数据集 我在数据集中创建一个 TableAdapter 查询如下 SELECT from Table WHERE ID IN IDs 现在如果

  • 如何引用下一行的数据？

    我正在 PostgreSQL 9 2 中编写一个函数 对于股票价格和日期的表 我想计算每个条目较前一天的百分比变化 对于最早一天的数据 不会有前一天 因此该条目可以简单地为 Nil 我知道WITH声明可能不应该高于IF陈述 到目前为止 这就

  • MySQL：如何获取每个分组的x个结果数[重复]

    这个问题在这里已经有答案了 可能的重复 mysql 在 GROUP BY 中使用 LIMIT 来获取每组 N 个结果 https stackoverflow com questions 2129693 mysql using limit w

  • Postgres JSON 数据类型 Rails 查询

    我正在使用 Postgres 的 json 数据类型 但想要使用嵌套在 json 中的数据进行查询 排序 我想在 json 数据类型上使用 where 进行订购或查询 例如 我想查询关注者数量 gt 500 的用户 或者我想按关注者或关注数

  • java库维护数据库结构

    我的应用程序一直在开发 所以偶尔 当版本升级时 需要创建 更改 删除一些表 修改一些数据等 通常需要执行一些sql代码 是否有一个 Java 库可用于使我的数据库结构保持最新 通过分析类似 db structure version 信息并执

  • SQL Server 2008 错误 233

    我正在使用以下 sql 脚本在 SQL Server 2008 中创建新登录名 CREATE LOGIN xyz WITH PASSWORD xyz DEFAULT DATABASE master DEFAULT LANGUAGE us e

  • 如何存储没有年份部分的生日？

    类似问题 Postgres 生日选择 https stackoverflow com questions 6913719 postgres birthdays selection 我们正在设计一项新功能 我们将存储人们生日的月份和日期部分

  • 以编程方式插入行（父行和子行）

    我正在使用 Spring 和 JDBCTemplate 该场景是 CUSTOMER 表和 ORDERS 表的父子关系 我想做一个插入 例如 1 个客户和 5 个订单 但我不确定如何以编程方式在 CUSTOMER 表中插入一行 如何获取 Or

  • 部分唯一索引不适用于冲突子句 PostgreSQL

    表结构 create table example a id integer b id integer c id integer flag integer 部分索引 create unique index u idx on example a

  • 如何在 Django QuerySet 中将 DateField() + TimeField() 转换为本地时间？

    我的模型为这些字段 date models DateField 开始时间 models TimeField 结束时间 models TimeField 我想用以下方式注释查询集start datetime and end datetime

  • 包含列和行总计的 SQL 数据透视表

    我正在尝试将行和列总计添加到该数据透视表中 create table test4 city nvarchar 10 race nvarchar 30 sex nvarchar 10 age int insert into test4 val

  • 选择多列 按一列分组 按计数排序

    我在Oracle中有以下数据集 c1 c2 c3 1A2 cat black 1G2 dog red B11 frog green 1G2 girl red 试图得到以下结果 基本上我首先尝试获取具有重复 c1 的行 c1 c2 c3 1G

  • 设置 MVC 下拉列表中日期的格式

    我的页面上有一个下拉列表 如下所示 Html DropDownList dd dates new SelectList Model seasonDates Please Select 其中 seasonDates 是日期的 IList 问题

  • 3 个表的 SQL 查询（或联接）

    第一次在 Stack Overflow 上问问题 很棒的资源 但是只有一件事真正让我作为 SQL 新手感到困惑 我有三个表 我想获取与鲍勃的学生相关的所有导师的姓名 表 1 教师 ID Name 1 Bob 表 2 学生 STUDENT I

  • SQL - 需要查找重复记录但排除反向事务

    我有一张交易表 偶尔会有 重复条目 如果 当管理员发现这些重复条目时 他们将撤销交易 从而创建负值 但由于监管要求 原始重复条目仍然保留 我想创建一个 SQL 查询 并使用 Crystal Reports 来制作报告 以便管理员轻松查找重复

  • 插入记录后如何从SQL Server获取Identity值

    我在数据库中添加一条记录identity价值 我想在插入后获取身份值 我不想通过存储过程来做到这一点 这是我的代码 SQLString INSERT INTO myTable SQLString Cal1 Cal2 Cal3 Cal4 SQ

  • 获取mysql中逗号分隔行中不同值的计数

    一个表 Jobs 有 2 列 JobId 城市 当我们保存工作时 工作位置可能是多个城市 如下所示 JobId City 1 New York 2 New York Ohio Virginia 3 New York Virginia 我如何

  • Oracle：按月分区表

    我的解决方案 德语几个月 PARTITION BY LIST to char GEBURTSDATUM Month PARTITION p1 VALUES JANUAR PARTITION p2 VALUES Februar PARTITI

  • 在Oracle中使用IW和MM

    我使用 IW 表示每周结果 使用 MM 表示每月结果 但我总是收到错误 ORA 00979 not a GROUP BY expression 00979 00000 not a GROUP BY expression 我的疑问是这些 We

随机推荐

  • 如何删除 XYLineAndShapeRenderer 中的点？

    我正在使用 JFreeChart 在 Java 中制作一个应用程序 它显示 XY 折线图 问题是它显示了线上数据集的每个点 而我不想显示这些点 关于如何删除这些点或使它们不可见有什么想法吗 这是一个示例屏幕截图 这是代码 JFrame fr

  • 如何用Java将日期插入MySQL数据库表？

    如何将没有时间的日期插入 MySQL 数据库表 我尝试了这些代码 但出现以下异常 com mysql jdbc exceptions jdbc4 MySQLSyntaxErrorException You have an error in

  • 使用 python 发送电子邮件，使用 csv 数据作为正文

    我正在使用 csv 库将数据提取到电子邮件正文中 我正在从 csv 中提取正文的某些列 我正在使用垃圾 Gmail 帐户进行测试 我只是对如何使用 for 循环感到困惑 如果我是正确的 您需要一个 for 循环来读取行 然后需要一个 for

  • 当针对“未定义”进行测试时，RegExp 给出了意外的结果

    我正在建造一个密码强度验证器检查密码是否包含小写和大写字符 我为此使用正则表达式 并在提供密码字符串时得到意外结果不明确的 请参阅下面的屏幕截图 我希望两张支票都能退回false 但第一个返回true Why does the first

  • 如何通过单击通知来关闭我的应用程序的任何活动？

    当我单击通知时 应用以下操作 intent setFlags Intent FLAG ACTIVITY NEW TASK Intent FLAG ACTIVITY CLEAR TOP startActivity intent 在应用程序的所

  • 使用 bash 读取两个文件的嵌套循环[重复]

    这个问题在这里已经有答案了 我目前正在尝试使用以下代码来合并两个输入文件 for i in cat file1 do for j in cat file2 do printf s s n i j done done 给定文件创建如下 pri

  • 将“mut”放在变量名之前和“:”之后有什么区别？

    以下是我在 Rust 文档中看到的两个函数签名 fn modify foo mut foo Box

  • Google Cloud Vertex AI - 模型不支持 400“dedicated_resources”

    我正在尝试使用 Python SDK 在 Google Cloud Platform 上部署通过 Vertex AI 训练的文本分类模型 from google cloud import aiplatform import os os en

  • Postgres：在 int 数组中查找最大值？

    使用 Postgres 9 3 有人可以解释一下为什么我不能直接在未嵌套的数组上使用 max 函数吗 据我了解 unnest 函数返回一个 setof 就像 select 语句一样 那么为什么这个查询的简短版本不起作用呢 我在概念上遗漏了一

  • 哪里是保存用户上传的图像的最佳位置

    我有一个显示画廊的网站 用户可以从网络上传自己的内容 通过输入 URL 或从计算机上传图片 我将 URL 存储在数据库中 这对于第一个用例来说效果很好 但如果用户从计算机上传 我需要弄清楚在哪里存储实际图像 这里有什么建议或关于我应该存储这

  • 如何在 Java 中初始化字节数组？

    我必须在 java 中以字节数组形式存储一些常量值 UUID 我想知道初始化这些静态数组的最佳方法是什么 这就是我目前正在做的 但我觉得一定有更好的方法 private static final byte CDRIVES new byte

  • 将 java.sql.Timestamp 转换为即时时间

    从我的数据库中我检索值如下 20 DEC 17 10 15 53 000000000 AM 我想要上面的java sql Timestamp转换为即时时间 2017 12 20T10 15 53Z 我尝试使用当前时间戳 Timestamp

  • rasa_nlu如何使用lookup_tables进行实体提取？

    我正在尝试使用 rasa nlu 和 rasa core 开发一个聊天机器人 但我没有得到 rasa nlu 如何使用 Lookup tables 进行实体提取的链接 我已经经历过 http blog rasa com improving

  • 使用 WMI 枚举音频输入设备

    我在我的 C 项目中使用 NAudio 我正在寻找一种枚举音频输入设备 麦克风等 的方法 这样我就可以获得它们的全名 不仅仅是我可以从 NAudio 获得的 31 个字符的长名称 我浏览了一些帖子 其中人们使用 WMI 枚举音频输出设备 M

  • 为什么这有效？删除多个 from 无子查询

    我不确定这是否是 SQL Server 2012 中的错误 我有一个简单的查询 DELETE FROM TABLE1 FROM TABLE2 WHERE TABLE1 COL1 1 在SSMS中 这段代码解析没有错误 并从Table1没有错

  • 如何在 WPF 中操作另一个类的窗口对象

    我是 WPF 和 C 新手 我了解很多 VB NET 并且习惯了调用文本框等表单对象的方式 我从另一个表单调用它 现在 我正在使用WPF 我很困惑 因为我有一个主窗口 我想从类将项目添加到主窗口中的列表框 在VB Net中 就是这样 IN

  • 当不在 python 中的正确包中时，不会捕获异常

    编辑 好的 我成功地隔离了该错误以及重现该错误的准确 完整的代码 但它要么是设计使然 要么是 python 中的错误 创建两个兄弟包 admin General 每个都有自己的 init py 当然 包装内admin将以下代码放入文件 te

  • 轻松使用 ASP.NET Identity 作为角色提供者

    我刚刚花了两天时间研究并使用现有数据库实现新的 ASP NET Identity 系统 更多信息请参见这里 将 ASP NET Identity 集成到现有的 DbContext 中 现在 我有一份工作UserStore and RoleS

  • 从 Kafka 消费失败迭代器处于失败状态

    我在使用来自 kafka 的消息时遇到异常 org springframework messaging MessagingException Consuming from Kafka failed nested exception is j

  • 每日查询计数，并具有多周的日期限制

    我正在尝试每天查找 个活跃用户 用户在创建后即处于活动状态多于每周 10 个请求 共 4 个连续几周 IE 2014 年 10 月 31 日 如果用户在以下时间段内每周发出的请求总数超过 10 个 则该用户处于活动状态 2014年10月24

热门标签