关键词:
【中文标题】SQL:自某个值首次出现以来的行数【英文标题】:SQL: Count of rows since certain value first occurred 【发布时间】:2018-10-23 21:55:02 【问题描述】:在 SQL Server 中,我试图计算自过去 5 天内首次观察到与今天相同的天气(假设今天是 2018 年 8 月 6 日)以来的天数。每个城镇。
这是数据:
+---------+---------+--------+--------+--------+
| Date | Toronto | Cairo | Zagreb | Ankara |
+---------+---------+--------+--------+--------+
| 1.08.18 | Rain | Sun | Clouds | Sun |
| 2.08.18 | Sun | Sun | Clouds | Sun |
| 3.08.18 | Rain | Sun | Clouds | Rain |
| 4.08.18 | Clouds | Sun | Clouds | Clouds |
| 5.08.18 | Rain | Clouds | Rain | Rain |
| 6.08.18 | Rain | Sun | Sun | Sun |
+---------+---------+--------+--------+--------+
这需要表现良好,但到目前为止我想出的只是针对每个城镇的单个查询(并且将有几十个城镇,而不仅仅是四个)。这可行,但不会扩展。
这是多伦多的...
SELECT
DATEDIFF(DAY, MIN([Date]), GETDATE()) + 1
FROM
(SELECT TOP 5 *
FROM Weather
WHERE [Date] <= GETDATE()
ORDER BY [Date] DESC) a
WHERE
Toronto = (SELECT TOP 1 Toronto
FROM Weather
WHERE DataDate = GETDATE())
...正确返回 4,因为今天有雨,过去 5 天内第一次下雨是 8 月 3 日。
但我想要返回的是这样的表格:
+---------+-------+--------+--------+
| Toronto | Cairo | Zagreb | Ankara |
+---------+-------+--------+--------+
| 4 | 5 | 1 | 5 |
+---------+-------+--------+--------+
这怎么可能?
【问题讨论】:
我希望这不是你的实际表结构。 :) 不!但它反映了我所面临的情况 - 简化了。 我认为你需要一个更规范化的表结构,例如“日期”、“城镇”、“天气”来实现这一点。 我考虑将其转换为垂直(理想情况下在查询内),但我没有取得任何进展。有什么提示吗?干杯 最好包含产生第一个数据透视输出的查询 【参考方案1】:您真的不想尝试对转轴数据执行此操作,虽然您声明数据不是以这种方式存储的,但您还没有向我们展示您是如何将城市转轴作为列的 -真可惜。
因此,我已在一个公用表表达式中“取消透视”样本,然后使用 apply operator
来计算前 5 天内相同天气的先前出现次数。看来您知道如何进行旋转,我将其留给您,然后再旋转最终结果。
with cte as (
select
date, city, weather
FROM (
SELECT * from mytable
) AS cp
UNPIVOT (
Weather FOR City IN (Toronto, Cairo, Zagreb, Ankara)
) AS up
)
select
date, city, weather, ca.prior
from cte
cross apply (
select count(*) as prior
from cte as prev
where prev.city = cte.city
and prev.date between dateadd(day,-6,cte.date) and dateadd(day,-1,cte.date)
and prev.weather = cte.weather
) ca
使用此示例数据:
CREATE TABLE mytable(
Date date NOT NULL
,Toronto VARCHAR(9) NOT NULL
,Cairo VARCHAR(9) NOT NULL
,Zagreb VARCHAR(9) NOT NULL
,Ankara VARCHAR(9) NOT NULL
);
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180801','Rain','Sun','Clouds','Sun');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180802','Sun','Sun','Clouds','Sun');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180803','Rain','Sun','Clouds','Rain');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180804','Clouds','Sun','Clouds','Clouds');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180805','Rain','Clouds','Rain','Rain');
INSERT INTO mytable(Date,Toronto,Cairo,Zagreb,Ankara) VALUES ('20180806','Rain','Sun','Sun','Sun');
上面的查询产生了这个结果:
+----+---------------------+---------+---------+-------+
| | date | city | weather | prior |
+----+---------------------+---------+---------+-------+
| 1 | 01.08.2018 00:00:00 | Ankara | Sun | 0 |
| 2 | 02.08.2018 00:00:00 | Ankara | Sun | 1 |
| 3 | 03.08.2018 00:00:00 | Ankara | Rain | 0 |
| 4 | 04.08.2018 00:00:00 | Ankara | Clouds | 0 |
| 5 | 05.08.2018 00:00:00 | Ankara | Rain | 1 |
| 6 | 06.08.2018 00:00:00 | Ankara | Sun | 2 |
| 7 | 01.08.2018 00:00:00 | Cairo | Sun | 0 |
| 8 | 02.08.2018 00:00:00 | Cairo | Sun | 1 |
| 9 | 03.08.2018 00:00:00 | Cairo | Sun | 2 |
| 10 | 04.08.2018 00:00:00 | Cairo | Sun | 3 |
| 11 | 05.08.2018 00:00:00 | Cairo | Clouds | 0 |
| 12 | 06.08.2018 00:00:00 | Cairo | Sun | 4 |
| 13 | 01.08.2018 00:00:00 | Toronto | Rain | 0 |
| 14 | 02.08.2018 00:00:00 | Toronto | Sun | 0 |
| 15 | 03.08.2018 00:00:00 | Toronto | Rain | 1 |
| 16 | 04.08.2018 00:00:00 | Toronto | Clouds | 0 |
| 17 | 05.08.2018 00:00:00 | Toronto | Rain | 2 |
| 18 | 06.08.2018 00:00:00 | Toronto | Rain | 3 |
| 19 | 01.08.2018 00:00:00 | Zagreb | Clouds | 0 |
| 20 | 02.08.2018 00:00:00 | Zagreb | Clouds | 1 |
| 21 | 03.08.2018 00:00:00 | Zagreb | Clouds | 2 |
| 22 | 04.08.2018 00:00:00 | Zagreb | Clouds | 3 |
| 23 | 05.08.2018 00:00:00 | Zagreb | Rain | 0 |
| 24 | 06.08.2018 00:00:00 | Zagreb | Sun | 0 |
+----+---------------------+---------+---------+-------+
自第一次出现以来的天数(过去 5 天内)
选择 日期、城市、天气、datediff(day,ca.prior,cte.date) 为先前 来自 cte 交叉申请( 选择 min(prev.date) 作为先前 从 cte 作为 prev 其中 prev.city = cte.city 和 dateadd(day,-6,cte.date) 和 dateadd(day,-1,cte.date) 之间的 prev.date 和 prev.weather = cte.weather ) 约【讨论】:
他有一个错误的期望结果,对于安卡拉来说应该是 3 而不是 2。 没有@Sami。 2 是正确的,因为只有最后 5 行是相关的。 不确定您所说的“您没有向我们展示您是如何将城市作为列的枢纽”的意思。这是一张桌子。所以是select * from CityWeather
@Peete 那么多伦多是怎么变成4的?嗯?
好点,@Sami。很抱歉造成混乱。澄清一下:这是关于自第一次出现与今天相同的天气以来已经过去了多少天,但仅在过去 5 天内。所以忽略 6 天前的所有记录。从剩下的 5 个中,找到第一个相同天气的记录,并返回从那天到现在的天数差异。所以在我想要的结果中,安卡拉确实是不正确的。应该是 5。我的错。我更新了原来的问题。【参考方案2】:
我想你想要类似的东西
CREATE TABLE T
(
[Date] DATE,
Toronto VARCHAR(45),
Cairo VARCHAR(45),
Zagreb VARCHAR(45),
Ankara VARCHAR(45)
);
INSERT INTO T VALUES
('2018-08-01', 'Rain', 'Sun', 'Clouds', 'Sun'),
('2018-08-02', 'Sun', 'Sun', 'Clouds', 'Sun'),
('2018-08-03', 'Rain', 'Sun', 'Clouds', 'Rain'),
('2018-08-04', 'Clouds', 'Sun', 'Clouds', 'Clouds'),
('2018-08-05', 'Rain', 'Clouds', 'Rain', 'Rain'),
('2018-08-06', 'Rain', 'Sun', 'Sun', 'Sun');
SELECT
(SELECT MAX(Occ) FROM (SELECT COUNT(Toronto) Occ FROM T WHERE Toronto = (select top 1 toronto from t order by date desc) GROUP BY Toronto) T) Toronto,
(SELECT MAX(Occ) FROM (SELECT COUNT(Cairo) Occ FROM T WHERE Cairo = (select top 1 Cairo from t order by date desc) GROUP BY Cairo) T) Cairo,
(SELECT MAX(Occ) FROM (SELECT COUNT(Zagreb) Occ FROM T WHERE Zagreb = (select top 1 Zagreb from t order by date desc)GROUP BY Zagreb) T) Zagreb,
(SELECT MAX(Occ) FROM (SELECT COUNT(Ankara) Occ FROM T WHERE Ankara = (select top 1 Ankara from t order by date desc)GROUP BY Ankara) T) Ankara
退货
+----+---------+-------+--------+--------+
| | Toronto | Cairo | Zagreb | Ankara |
+----+---------+-------+--------+--------+
| 1 | 4 | 5 | 1 | 3 |
+----+---------+-------+--------+--------+
Demo
【讨论】:
谢谢。我要避免的是在自己的子查询中分别指定每个城镇。有几十个,而且名单还在增加。这也会返回出现最多的天气类型的计数,而不是与今天天气匹配的天气类型(例如,当萨格勒布应该计算“太阳”时,它会返回“云”)。它仍然可以帮助我自己找到完整的解决方案。干杯 @Peete 检查更新,但对于安卡拉来说,最后一个天气是太阳,所以应该是 3 而不是 2。 越来越近了,太好了。然而,安卡拉是 2,因为只有最后 5 行是相关的。但这是一个很小的变化。获取过去 7 天以来的行数
】获取过去7天以来的行数【英文标题】:Getcountofrowssincethepast7days【发布时间】:2019-12-1219:08:42【问题描述】:我正在尝试设置一个MySQL查询,以获取自MySQL数据库自过去7天以来添加的行数的指标。(使用BigQuery)为简单起见,我... 查看详情
PL/SQL ORA-01422:精确提取返回的行数超过了请求的行数
...不断收到此错误,我无法弄清楚是什么问题。声明*第1行出现错误:ORA-01422:精确提取返回的行数多于请求的行数ORA-06512:在第11行这是我的代码。DECLA 查看详情
ORA-00903,ORA-06512,同时计算所有用户表的行数(动态 sql)
...描述】:我想统计每个用户表的行数,但是通过动态sql,出现这样的错误。ORA-00903:无效的表名ORA-06512:在第19行SQL2.sql2 查看详情
计算本周 SQLite 数据库中的行数
...的图书数量。是否可以修改查询,使其仅返回自本周开始以来输入的人数?publicintGetWeeklyCount()varweek=DateTime.Today.AddDays(-7);re 查看详情
linux输出文件中包含某个字符串的行数(代码片段)
1、linux输出文件中包含某个字符串的行数命令:grep‘需要查询的字符串’文件名[root@localhostxz]$grep'WARN'test.log 查看详情
SQL 计算表中的行数
】SQL计算表中的行数【英文标题】:SQLcountrowsinatable【发布时间】:2015-05-0903:04:45【问题描述】:我需要向数据库发送一个SQL查询,告诉我一个表中有多少行。我可以使用SELECT获取表中的所有行,然后计算它们,但我不喜欢这样... 查看详情
sql所有表的行数(代码片段)
执行触发器时,精确获取返回的行数超过请求的行数 SQL*PLUS
】执行触发器时,精确获取返回的行数超过请求的行数SQL*PLUS【英文标题】:exactfetchreturnsmorethanrequestnumberofrowswhentriggerisexecutedSQL*PLUS【发布时间】:2013-12-0818:00:44【问题描述】:我需要将触发器合并到我的一个使用光标的脚本中... 查看详情
如何获取受 SQL Alchemy 影响的行数?
】如何获取受SQLAlchemy影响的行数?【英文标题】:HowdoIgetthenumberofrowsaffectedwithSQLAlchemy?【发布时间】:2012-10-0109:45:55【问题描述】:如何使用sqlalchemy获取更新语句受影响的行数?(我正在使用mysql和python/pyramid):fromsqlalchemy.engi... 查看详情
计算 SQL 中值转换之间的行数
】计算SQL中值转换之间的行数【英文标题】:CountingthenumberofrowsbetweentransitionsofvaluesinSQL【发布时间】:2021-01-2821:59:36【问题描述】:我有带有user_id、时间戳和是/否答案的行。我想计算每个ID有多少条“NO”(连续行)。例子:use... 查看详情
如何在 Oracle PL/SQL 中处理“超过请求的行数”
】如何在OraclePL/SQL中处理“超过请求的行数”【英文标题】:Howtohandle"morethanrequestednumberofrows"inOraclePL/SQL【发布时间】:2021-02-0817:31:50【问题描述】:如果员工工作超过18年,我的代码会打印“是”,否则会打印“否”。... 查看详情
如何提取 Count(*) 中的行数,并发送到变量。 (PL/SQL)
】如何提取Count(*)中的行数,并发送到变量。(PL/SQL)【英文标题】:HowdoyouextractnumberofrowsinCount(*),andsendtoavariable.(Pl/SQL)【发布时间】:2013-06-1113:06:51【问题描述】:我正在尝试根据其条件使用count(*)提取行数,并将其发送到变量以... 查看详情
如何使用 DATEDIFF() 返回自添加以来已超过 24 小时的行
】如何使用DATEDIFF()返回自添加以来已超过24小时的行【英文标题】:HowcanIuseDATEDIFF()toreturnrowswhereithasbeenmorethan24hourssinceitwasadded【发布时间】:2017-08-1607:08:27【问题描述】:我有一个名为date_added的列在MySQL中存储为日期时间的表... 查看详情
SQL - 计算包含两个不同字段的行数
】SQL-计算包含两个不同字段的行数【英文标题】:SQL-Countingthenumberofrowsthatcontaintwodistinctfields【发布时间】:2012-07-1914:37:48【问题描述】:是否有任何方法可以获取fieldB的计数,但仅限于fieldA和fieldB组合唯一的情况?例如fieldAfiel... 查看详情
在一个查询中计算多个表的行数
...时间】:2016-03-3119:38:42【问题描述】:所以我有大量属于某个类别的表格。这些表都以B1T开头(所以B1T00001、B1T0002等)。我的任务是提供有关这组表格的一些信息。这是我的初始SQL,用于计算有多少B1T表。selectobj_nmfromod_md_objwhere... 查看详情
sql如何查询出某一列中不同值出现的次数?
...按照行数倒序排列。参考技术ASQL查询出某一列中不同值出现次数的步骤如下:我们需要准备的材料分别是:电脑、sql查询器。1、首先,打开sql查询器,连接上相应的数据库表,例如test2表。2、点击“查询”按钮,输入:selectA,co... 查看详情
如何计算sql中where语句中的行数?
】如何计算sql中where语句中的行数?【英文标题】:Howtocalculaterowscountinwherestatementinsql?【发布时间】:2021-11-0219:12:12【问题描述】:我在SQLServer中有两个表:order(列:order_id、payment_id)payment(列:payment_id、is_pay)我想获取所有... 查看详情
Pyspark sql 计数返回的行数与纯 sql 不同
】Pysparksql计数返回的行数与纯sql不同【英文标题】:Pysparksqlcountreturnsdifferentnumberofrowsthanpuresql【发布时间】:2018-02-0609:29:07【问题描述】:我已经开始在我的一个项目中使用pyspark。我正在测试不同的命令来探索库的功能,但我... 查看详情