sql - 在 SQL 中添加新列,CONCAT 过去期间(日期范围)的值尊重组
问题描述
我正在使用 MS SQL Server 2012 检查用户是否在过去一段时间内为每个事务/行选择了特定类型的电影。
我想通过将过去的流派连接到给定时期的新列来解决这个问题,例如过去 12 个月或 6 到 12 个月。我不关心出现在新列中的重复类型。
然后我可以运行逻辑来检查特定流派是在 SQL 中还是由外部程序订购的。下面是两个订购视频的客户的预期输出示例。
这是我用来生成初始表的 SQL。
DROP TABLE IF EXISTS tb1;
CREATE TABLE tbl
(id int IDENTITY(1,1) PRIMARY KEY,
PersonName VARCHAR(10),
OrderDT DATETIME,
Genre VARCHAR(10));
INSERT INTO tbl (PersonName, OrderDT, Genre)
VALUES
('Bob', '2019-05-01', 'biopic'),
('Bob', '2019-10-01', 'drama'),
('Bob', '2020-02-01', 'doco, action' ),
('Bob', '2020-05-01', 'comedy'),
('Bob', '2020-09-01', 'horror'),
('Bob', '2020-12-01', 'action, comedy'),
('Alice', '2018-03-01', 'comedy, drama'),
('Alice', '2018-08-01', 'action'),
('Alice', '2018-12-01', 'horror');
我知道使用 STRING_AGG() 和 GROUP BY 可用于聚合每个客户的值行,但我需要帮助才能在过去的一段时间和每一行中执行此操作。
即使我使用的是 MS SQL(通过我自己的选择:))我希望该解决方案足够通用,可以在 Postgres 或 MariaDB 中使用。因此,如果可能的话,我想避免像 STRING_AGG() 这样的特殊 MS SQL 服务器命令。
解决方案
这个问题在 SQL Server 中已经够难了。您不应该在一个字符串中存储多个流派。你提到string_agg()
,所以你显然没有使用 SQL Server 2012。这是在 SQL Server 的更新版本中工作的逻辑:
select t.*, t12.*, t6.*
from tbl t cross apply
(select string_agg( trim(g.genre), ', ') as orders_12mon
from (select distinct s.value as genre
from tbl t2 cross apply
string_split(genre, ',') s
where t2.personname = t.personname and
t2.orderdt <= t.orderdt and
t2.orderdt > dateadd(month, -12, t.orderdt)
) g
) t12 cross apply
(select string_agg( trim(g.genre), ', ') as orders_6mon
from (select distinct s.value as genre
from tbl t2 cross apply
string_split(genre, ',') s
where t2.personname = t.personname and
t2.orderdt > dateadd(month, -12, t.orderdt) and
t2.orderdt <= dateadd(month, -6, t.orderdt)
) g
) t6;
这是一个 db<>fiddle。
此代码是高度不可移植的。以下是一些原因:
- 日期/时间函数在不同的数据库中是不同的。在您提到的数据库中,没有一致的方法可以减去 6 个月。
- SQL Server 不支持
range
带时间间隔的关键字。 - 字符串处理函数在数据库中完全不同,因此拆分字符串不是标准的。
也就是说,如果您事先了解所有类型,我可以想到一种神秘的方法。但这确实很混乱,您提到的每个数据库中都有更简单的方法。
推荐阅读
- javascript - ngModel 绑定在页面部分不起作用
- php - 如何为 Google AdWords API 的广告组增加/减少移动出价标准?
- javascript - 如何在javascript中使firebase数据库中的变量成为全局变量
- python - 叠加两个 seaborn 因子图
- docker - 如何在不使用 Docker Hub 的情况下将我的应用程序部署到容器操作系统?
- java - 多个泛型边界,包括类型参数
- android - Android WifiManager::getScanResults() 仍然返回空列表
- ruby-on-rails - 在 shopify 片段目录中创建液体文件 - Rails 应用程序
- reactjs - 组件导入后 react-bootstrap 未获取表单标签
- java - 正则表达式,删除空格和所有其他字符