找到持续三年的注意力的用户 [英] Find user having continuously three year of attendenance

问题描述

我有一个事件表，其中将存储参与的用户列表。



示例数据：

╔═══════════════════╗b$ b║id║name║year║ $ b $b╠════════════════════b $ b║ 1 ║Arun║ 2001  $ $ b $b║ 2 ║Arun║ 2002 ║
║ 3 ║Arun║ 2003 ║
║ 4 ║Arun║ 2004 ║
║ 5  ║Arun║ 2009 ║
║ 6 ║Arun║ 2010 ║
║ 7 ║Arun║ 2011 ║$ b $b║ 8 ║Bala║ 2014 ║
║ 9 ║Bala║ 2015 ║
║ 10 ║巴拉║ 2017 ║
║ 11 ║chan║ 2014 ║$ b $b║ 12 ║chan║ 2015 ║$ b $b║ 13 ║chan║ 2018 ║
╚═════════════════════════════════════ ═════════════<
 
 
 
 
 
 
我只需找到用户新专栏多年来连续三年参加。即使用户连续参加了6年，也必须分成一列中的前三个，另一列中的下三个。 
 
 
 
 输出： 
 
 
 
 
╔ ════════════════b $ b║id║name║years║
╠══════════ ═╬════════════b $ b║ 1 ║Arun║2001-2003║
║ 2 ║Arun║2009-2011║
╚════════════════════════════════════════════════════════════ p $ p> 
解决方案
 
此查询应解决您的问题：
 
 
 
 
 选择 ROW_NUMBER（） over （ ORDER   BY 最小（id）） AS 数字，名称，演员表（分钟（年） as   varchar （ 100 ））+ '   -   + cast（max（year） as   varchar （ 100 ）） as 年
 来自（
 选择 e。*，（year  -  row_number（）  over （分区 按名称订单 按年）） as  grp 来自 tblStudent e 
）e 
  group   by  name，grp 
   count（*）> =  3 ; 
 
 
 
使用你的年份 -  row_number（）结束，你可以做一个额外的模数计算3然后f ilter with 0. 
 
 
 
这样的事情：
 
 
  事件为（
   -   设置虚拟数据 
 选择 
  1  id，
 '  Arun'名称，
  2001 年
  union  全部 选择  2 ，'  Arun'， 2002  
  union  全部 选择  3 ，'  Arun'， 2003  
  union  所有 选择  4 ，'  Arun'， 2004  
  union  所有 选择  5 ， '  Arun'， 2009  
  union  所有 选择  6 ，'  Arun'， 2010  
  union  所有 选择  7 ，'  Arun'， 2011  
  union  全部 选择 < span class =code-digit> 8 ，'  Bala'， 2014  
  union   all  选择  9 ，'  Bala'， 2015  
  union  所有 选择  10 ，'   Bala'， 2017  
  union  所有 选择  11 ，'  chan'， 2014  
  union  所有 选择  12 ， '  chan'， 2015  
  union  所有 选择  13 ，'  chan'， 2018  
   -   额外数据 
 < span class =code-keyword> union   all   select   14 ，'  Arun'， 2006  
 < span class =code-keyword> union   all   select   15 ，'  Arun'， 2005  
  union   all  选择  16 ，'  Bala'， 2018  
  union   all  选择  17 ，' < span class =code-string> Bala'， 2020  
  union  全部 选择  18 ，'  Bala'， 2019  
  union  < span class =code-keyword> all  选择  22 ，'  Jim'， 2009  
  union  所有 选择  21 ，'  Jim'， 2010  
  union  所有 选择  20 ，'  Jim' ， 2011  
 
），eventsGroup  as （
   -   < span class =code-comment>你的sql的一部分 
 选择 
 e。*，
（年份 -  row_number（） over （分区  by  name  order   by  year）） as  grp 
 来自事件e 
），eventsGroup3Year  as （
   -   进行模数检查 
 选择 
 *，
（row_number（） over （ partition  按名称，grp 订单 按年））％3  as  rowid3 
 来自 eventsGroup 
）
 选择 
 row_number（） over （ order  按名称，年份）ID，
名称，
 转换 （ char （ 4 ），year-2）+ '    - ' + convert（ char （ 4 ），年）年
 来自 eventsGroup3Year 
 其中 rowid3 =  0  
 订单 按 
名称，
年
; 
 
 
如果您有重复值，它可能无效，
 
 let再说一行Arun，2003年 - 你需要测试一下。
 
 
 
希望有所帮助。
 
 ----- 
 
额外的sql [出于某种原因，这个问题一直困在我脑海里:)]：
 
要获得同一行/记录中的开始和结束年份，你可以使用LAG分析函数（ms sql 2012向上）。
 
 
 
这样的东西：
 
 
  with （
   -    ... cte如上，最多到eventsGroup3Year ...  
）
，LagYear  as （
 选择 
 *，
滞后（年， 1 ， 0 ） over （ order  按名称，年）StartYear 
 来自 eventsGroup3Year 
  w这里 rowid3 =  0  或 rowid3 =  1  
）
 选择 
 row_number（） over  （ order   by  name，year）id，
 name，
 concat（StartYear ，'   - '，年）年  -    concat via solution 3，感谢Maciej Los  
 来自 LagYear  where  rowid3 =  0  
; 
 
 
 LAG（Transact- SQL）： https://msdn.microsoft.com/en-us/library/hh231256.aspx [ ^ ] 
 
 
另一个解决方案：
 
 
  DECLARE   @ tmp  表（ID  INT ，[Name]  VARCHAR （ 30 ），[年]  INT ）
 
 < span class =code-keyword> INSERT   INTO   @ tmp （ID，[Name ]，[年]）
  VALUES （ 1 ，'  Arun'， 2001 ），
（ 2 ，'  Arun'， 2002 ），
（ 3 ，'  Arun'， 200 3 ），
（ 4 ，'  Arun'， 2004 ），
（ 5 ，'  Arun'， 2009 ），
（ 6 ，'  Arun'，  2010 ），
（ 7 ，'  Arun'， 2011 ），
（ 8 ，'  Bala'， 2014 ），
（ 9 ，'  Bala'， 2015 ），
（ 10 ，'  Bala'， 2017 ），
（ 11 ，'  chan'， 2014 ），
（ 12 ，'  chan'， 2015 ），
（ 13 ，'  chan'， 2018  ），
（ 14 ，'  zen' ， 2014 ），
（ 15 ，'  zen'， 2015 ）
 
 < span class =code-comment>  -    4  
  SELECT  [姓名]，C ONCAT（[ 1 ]，'   - '，[ 3 ]） AS 年
  FROM （
   -    3  
  SELECT  [Name]，[Grp]，[ 1 ]，[ 2 ]，[ 3 ] 
  FROM （
   -    2  
  SELECT  [名称]，[年]，Grp，ROW_NUMBER（） OVER （ PARTITION   BY  [名称]，[Grp]  ORDER   BY  [年]） AS  RowNo 
  FROM （
   -    1  
  SELECT  [Name]，[Year]，[Year]  -  ROW_NUMBER（） OVER （ PARTITION   BY  [名称]  ORDER   BY  [年]） AS  Grp 
  FROM   @tmp  
） AS  T 
） AS  SRC 
 PIVOT（MAX（[年]） FOR  [RowNo]  IN （[ 1 ]，[ 2 ]，[ 3 ]） ） AS  PVT 
  WHERE   COALESCE （[ 3  ]， 0 ） -   COALESCE （[ 1 ]， 0 ）=  2  
） AS  DST 
 
 
 
 
关注查询的作用是什么？
 
 1.查询创建组连续几年
 
 2.查询为每个组添加行号
 
 3.查询创建数据透视表并仅返回减法结果等于2的数据
 
 4.查询 - 最终查询 - 连接年份
 
 
I have a event table in which the list of user participated will be stored.



Sample Data:



╔════╦══════╦══════╗
║ id ║ name ║ year ║
╠════╬══════╬══════╣
║  1 ║ Arun ║ 2001 ║
║  2 ║ Arun ║ 2002 ║
║  3 ║ Arun ║ 2003 ║
║  4 ║ Arun ║ 2004 ║
║  5 ║ Arun ║ 2009 ║
║  6 ║ Arun ║ 2010 ║
║  7 ║ Arun ║ 2011 ║
║  8 ║ Bala ║ 2014 ║
║  9 ║ Bala ║ 2015 ║
║ 10 ║ Bala ║ 2017 ║
║ 11 ║ chan ║ 2014 ║
║ 12 ║ chan ║ 2015 ║
║ 13 ║ chan ║ 2018 ║
╚════╩══════╩══════╝






I need to find only the user who have attended continuously for three years with the years in new column. Even if the user attended for continuous 6 years then have to split into first three in an column and next three in another column. 



Output:



╔════╦══════╦═══════════╗
║ id ║ name ║   years   ║
╠════╬══════╬═══════════╣
║  1 ║ Arun ║ 2001-2003 ║
║  2 ║ Arun ║ 2009-2011 ║
╚════╩══════╩═══════════╝
解决方案
This query should fix your problem:



select  ROW_NUMBER() over (ORDER BY Min(id)) AS Number, name, cast( min(year) as varchar(100)) + ' - ' + cast(max(year) as varchar(100)) as years
from (
		select e.*, (year - row_number() over (partition by name order by year)) as grp from tblStudent e
) e
group by name, grp
having count(*) >= 3;

Using your year - row_number() over, you could do an additional modulus calculation of 3 and then filter with 0.



Something like this:

with events as(
--setup dummy data
	select 
		1 id,
		'Arun' name,
		2001 year
	union all select 2, 'Arun', 2002
	union all select 3, 'Arun', 2003
	union all select 4, 'Arun', 2004
	union all select 5, 'Arun', 2009
	union all select 6, 'Arun', 2010
	union all select 7, 'Arun', 2011
	union all select 8, 'Bala', 2014
	union all select 9, 'Bala', 2015
	union all select 10, 'Bala', 2017
	union all select 11, 'chan', 2014
	union all select 12, 'chan', 2015
	union all select 13, 'chan', 2018
--extra data
	union all select 14, 'Arun', 2006
	union all select 15, 'Arun', 2005
	union all select 16, 'Bala', 2018
	union all select 17, 'Bala', 2020
	union all select 18, 'Bala', 2019
	union all select 22, 'Jim', 2009
	union all select 21, 'Jim', 2010
	union all select 20, 'Jim', 2011

), eventsGroup as (
	--part of your sql
	select
		e.*, 
		(year - row_number() over (partition by name order by year)) as grp 
	from events e
), eventsGroup3Year as (
	--do a modulus check
	select
		*, 
		(row_number() over (partition by name, grp order by year))%3 as rowid3
	from eventsGroup
)
select 
	row_number() over(order by  name, year) id,
	name,
	convert(char(4), year-2)+' - '+convert(char(4), year) years
from eventsGroup3Year
where rowid3 = 0
order by
	name,
	year
;


It will probably not work if you have duplicate values,

lets say an additional row of 'Arun', 2003 - you will need to test that out.



Hope that helps out.

-----

Additional sql [for some reason this question has gotten stuck in my head :)]:

To get the start and end year in the same row/record, you could use the LAG analytic function (ms sql 2012 upwards).



Something like this:

with (
--	...cte as above, up to eventsGroup3Year...
)
, LagYear as(
select 
	*,
	lag(year, 1, 0) over (order by name, year) StartYear
from eventsGroup3Year
where rowid3 = 0 or rowid3 = 1
)
select
	row_number() over(order by name, year) id,
	name,
	concat(StartYear, ' - ', year) Years -- concat via solution 3, thanks Maciej Los
from LagYear where rowid3 = 0
;


LAG (Transact-SQL): https://msdn.microsoft.com/en-us/library/hh231256.aspx[^]

Another solution:

DECLARE @tmp TABLE(ID INT, [Name] VARCHAR(30), [Year] INT)

INSERT INTO @tmp (ID, [Name], [Year])
VALUES(1, 'Arun', 2001),
(2, 'Arun', 2002),
(3, 'Arun', 2003),
(4, 'Arun', 2004),
(5, 'Arun', 2009),
(6, 'Arun', 2010),
(7, 'Arun', 2011),
(8, 'Bala', 2014),
(9, 'Bala', 2015),
(10, 'Bala', 2017),
(11, 'chan', 2014),
(12, 'chan', 2015),
(13, 'chan', 2018),
(14, 'zen', 2014),
(15, 'zen', 2015)

--4
SELECT [Name], CONCAT([1], ' - ', [3]) AS Years
FROM (
	--3
	SELECT [Name], [Grp], [1], [2], [3]
	FROM (
		--2
		SELECT [Name], [Year], Grp, ROW_NUMBER() OVER(PARTITION BY [Name], [Grp] ORDER BY [Year]) AS RowNo
		FROM (
			--1
			SELECT [Name], [Year], [Year] - ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY [Year]) AS Grp
			FROM @tmp
		) AS T
	) AS SRC
	PIVOT(MAX([Year]) FOR [RowNo] IN ([1], [2], [3])) AS PVT
	WHERE COALESCE([3],0) - COALESCE([1],0) = 2
) AS DST




What the followig queries do?

1. query creates "group" for consecutive years

2. query add row number for each group

3. query creates pivot table and returns only those data which the result of substraction is equal 2

4. query - final query - concatenates years


                        
这篇关于找到持续三年的注意力的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！