当前在线人数13935
首页 - 分类讨论区 - 电脑网络 - 数据库版 - 同主题阅读文章

此篇文章共收到打赏
0

  • 10
  • 20
  • 50
  • 100
您目前伪币余额:0
未名交友
[更多]
[更多]
求SQL 难题
[版面:数据库][首篇作者:olive2008] , 2017年03月01日17:37:59 ,1760次阅读,20次回复
来APP回复,赚取更多伪币 关注本站公众号:
[分页:1 ]
olive2008
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 1 ]

发信人: olive2008 (olive), 信区: Database
标  题: 求SQL 难题
发信站: BBS 未名空间站 (Wed Mar  1 17:37:59 2017, 美东)

用的是 SQL SERVER 2014
数据仓库里的维度表需要将相同的row 合并,同时update 起止日期。
因为表很大,大约300个column,几十个M 的记录。
所以我用了Binary_CheckSum。 现在得出下面的记录。col1,col2只是节选,有200多
个。
BinaryCheckSum 只计算col1,col2 等等需要比较的attributes。

下面的记录,1和2需要合并成一行,start 20161017, end 20170105.
5 和6 需要合并成一行,start 20170126, end NULL

我初衷是想用BinaryCheckSum来合并,但是1和2合并就会把4也合并了,是不对的。

如果数据量小,可以有很多办法做,但是表非常大,不想用selfjoin,merge等,太难
写了。
有什么好办法?下午在办公室想的头疼。

Id  GroupId  col1  col2    Start      End     BinaryCheckSUm
1     111    aaa   bbb   20161017   20161214    1455015
2     111    aaa   bbb   20161215   20170105    1455015
3     111    aa1   bbb   20170106   20170111    67850
4     111    aaa   bbb   20170112   20170125    1455015
5     111    aa1   bbb   20170126   20170215    67850
6     111    aa1   bbb   20170216   NULL        67850
7     112    ccc   dd2   20161117   20161208    45345

十分感谢!
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 98.]

 
olive2008
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 2 ]

发信人: olive2008 (olive), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Wed Mar  1 21:02:14 2017, 美东)

补充一下,上面排好的记录,同一个groupId里面起止日期都是相连的没有空期。

就是说,把 BinaryCheckSum 相同 并且是连续相邻的 row 合并在一起。
可是,这个“连续相邻” 的关系怎么用表达式表达出来呢?

--
※ 修改:·olive2008 於 Mar  1 21:11:10 2017 修改本文·[FROM: 98.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 98.]

 
badboy07
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 3 ]

发信人: badboy07 (又见飞刀), 信区: Database
标  题: Re: 姹係QL 闅鹃
发信站: BBS 未名空间站 (Thu Mar  2 02:48:17 2017, 美东)

Store procedure?
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 47.]

 
lavahx
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 4 ]

发信人: lavahx (见面就跑), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Thu Mar  2 05:21:49 2017, 美东)

除非是实时表,有大量数据涌入,否则直接写个程序处理多容易呀。
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 125.]

 
ocean001
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 5 ]

发信人: ocean001 (ocean), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Thu Mar  2 20:28:04 2017, 美东)

可以试试 cte + windows function lag() over (), 可以小批量测试performance
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 73.]

 
heehee
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 6 ]

发信人: heehee (雨过天晴), 信区: Database
标  题: Re: 姹係QL 闅鹃
发信站: BBS 未名空间站 (Thu Mar  2 23:07:41 2017, 美东)

Select

groupid,col1,col2,min(startdate ),max(enddate),binarychecksum

From table

Group by groupid,col1,col2,binarychecksum

Having max(startdate) = dateadd(day,1,min(enddate))


--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 24.]

 
heehee
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 7 ]

发信人: heehee (雨过天晴), 信区: Database
标  题: Re: 姹係QL 闅鹃
发信站: BBS 未名空间站 (Thu Mar  2 23:13:19 2017, 美东)

Run your query execution plan for some data and see if there is any missing
indexes before running it on your big table. Always check if you have enough
tempdb and log space.

You might be able to divide them into small batches by using where
conditions in the id range.





--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 24.]

 
olive2008
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 8 ]

发信人: olive2008 (olive), 信区: Database
标  题: Re: 姹係QL 闅鹃
发信站: BBS 未名空间站 (Fri Mar  3 08:55:58 2017, 美东)

你这样group会把1,2,4 group在一起,然后having的时候又被filter掉了,等于1,2,4
这三行全没了。

【 在 heehee (雨过天晴) 的大作中提到: 】
: Select
: groupid,col1,col2,min(startdate ),max(enddate),binarychecksum
: From table
: Group by groupid,col1,col2,binarychecksum
: Having max(startdate) = dateadd(day,1,min(enddate))



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 98.]

 
anmitbbsguy
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 9 ]

发信人: anmitbbsguy (a bbs guy), 信区: Database
标  题: Re: 姹係QL 闅鹃
发信站: BBS 未名空间站 (Fri Mar  3 20:09:32 2017, 美东)

你说得对. 早上看到这段的时候就觉得有问题.

【 在 olive2008 (olive) 的大作中提到: 】
: 你这样group会把1,2,4 group在一起,然后having的时候又被filter掉了,等于1,2,
4
: 这三行全没了。



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 147.]

 
anmitbbsguy
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 10 ]

发信人: anmitbbsguy (a bbs guy), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Fri Mar  3 20:15:26 2017, 美东)

用Cursor对每一个Binary_CheckSum选出小数据集, 再self join?

用数据库的优势就在于能用SQL join,否则的话跟写程序就没区别了.


【 在 olive2008 (olive) 的大作中提到: 】
: 用的是 SQL SERVER 2014
: 数据仓库里的维度表需要将相同的row 合并,同时update 起止日期。
: 因为表很大,大约300个column,几十个M 的记录。
: 所以我用了Binary_CheckSum。 现在得出下面的记录。col1,col2只是节选,有200多
: 个。
: BinaryCheckSum 只计算col1,col2 等等需要比较的attributes。
: 下面的记录,1和2需要合并成一行,start 20161017, end 20170105.
: 5 和6 需要合并成一行,start 20170126, end NULL
: 我初衷是想用BinaryCheckSum来合并,但是1和2合并就会把4也合并了,是不对的。
: 如果数据量小,可以有很多办法做,但是表非常大,不想用selfjoin,merge等,太难
: ...................



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 147.]

 
olive2008
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 11 ]

发信人: olive2008 (olive), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Fri Mar  3 21:14:54 2017, 美东)

用cursor的话都不需要self join了。从第一行开始给个行号为1,第二行如果checksum
和上一行一样就还是1,不一样加 1,这样每次都是每两行进行比较,以此类推就行了
。然后按行号和checksum group就行了。
想找到不用cursor 的方法,因为表太大了。找不到就用cursor了,毕竟再多数据,也
只是简单的加1的操作。

我在解这个问题的时候就体会到,SQL server 的window function 缺少一个running
ranking的功能,就是窗口分割好以
后,每个窗口里面逐行比较。类似running summary的功能。希望微软以后能加上。

【 在 anmitbbsguy (a bbs guy) 的大作中提到: 】
: 用Cursor对每一个Binary_CheckSum选出小数据集, 再self join?
: 用数据库的优势就在于能用SQL join,否则的话跟写程序就没区别了.




--
※ 修改:·olive2008 於 Mar  3 21:17:07 2017 修改本文·[FROM: 98.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 98.]

 
OKOK
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 12 ]

发信人: OKOK (  好吧,就这样吧), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sat Mar  4 00:40:30 2017, 美东)

提个思路吧。

- 复制这个table,但set NewStart = ISNULL(dateadd(d,1,End), Start), Id的值改一
下, 这样你可以得到

Id  GroupId  col1  col2   NewStart      End     BinaryCheckSUm
A1     111    aaa   bbb   20161215    20161214    1455015
A2     111    aaa   bbb   20170106    20170105    1455015
A3     111    aa1   bbb   20170112    20170111    67850
A4     111    aaa   bbb   20170126    20170125    1455015
A5     111    aa1   bbb   20170216    20170215    67850
A6     111    aa1   bbb   20170216    NULL        67850
A7     112    ccc   dd2   20161209    20161208    45345

- Combine two tables using 'UNION ALL'

- 把 Start列加到binary_checksum 的expression里,重新计算checksum

- checksum 相同的表示存在‘连续相邻’, 例如

Id  GroupId   col1  col2  Start      End         BinaryCheckSUm
2     111    aaa   bbb   20161215   20170105    *******
A1     111    aaa   bbb   20161215   20161214    *******

- 以下的分离,合并等应该很容易了。

【 在 olive2008 (olive) 的大作中提到: 】
: 用的是 SQL SERVER 2014
: 数据仓库里的维度表需要将相同的row 合并,同时update 起止日期。
: 因为表很大,大约300个column,几十个M 的记录。
: 所以我用了Binary_CheckSum。 现在得出下面的记录。col1,col2只是节选,有200多
: 个。
: BinaryCheckSum 只计算col1,col2 等等需要比较的attributes。
: 下面的记录,1和2需要合并成一行,start 20161017, end 20170105.
: 5 和6 需要合并成一行,start 20170126, end NULL
: 我初衷是想用BinaryCheckSum来合并,但是1和2合并就会把4也合并了,是不对的。
: 如果数据量小,可以有很多办法做,但是表非常大,不想用selfjoin,merge等,太难
: ...................



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 74.]

 
matt1981
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 13 ]

发信人: matt1981 (害虫007), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sat Mar  4 01:08:47 2017, 美东)

如果一个项目不只两行,有3行以上 你的方法可行吗?


【 在 OKOK (  好吧,就这样吧) 的大作中提到: 】
: 提个思路吧。
: - 复制这个table,但set NewStart = ISNULL(dateadd(d,1,End), Start), Id的值改一
: 下, 这样你可以得到
: Id  GroupId  col1  col2   NewStart      End     BinaryCheckSUm
: A1     111    aaa   bbb   20161215    20161214    1455015
: A2     111    aaa   bbb   20170106    20170105    1455015
: A3     111    aa1   bbb   20170112    20170111    67850
: A4     111    aaa   bbb   20170126    20170125    1455015
: A5     111    aa1   bbb   20170216    20170215    67850
: A6     111    aa1   bbb   20170216    NULL        67850
: ...................



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2601:0196:4701:]

 
OKOK
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 14 ]

发信人: OKOK (  好吧,就这样吧), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sat Mar  4 09:29:35 2017, 美东)

这个方法很繁琐,解决楼主的简单例子也许可行,但对于同一GroupID里有多个不连续
dupeset的情况又有一番周折,这样意思就不大了。也许可以试试recursive query.

【 在 matt1981 (害虫007) 的大作中提到: 】
: 如果一个项目不只两行,有3行以上 你的方法可行吗?



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 74.]

 
matt1981
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 15 ]

发信人: matt1981 (害虫007), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sat Mar  4 12:37:03 2017, 美东)

之前代码有问题,对题目理解不正确,想多了





--
※ 修改:·matt1981 於 Mar  4 14:40:26 2017 修改本文·[FROM: 2601:0196:4701:9]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2601:0196:4701:]

 
anmitbbsguy
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 16 ]

发信人: anmitbbsguy (a bbs guy), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sat Mar  4 12:52:49 2017, 美东)

我的意思是说 divide and conquer. 对每一个checksum, 把对应的记录单独选出来处
理, 比如说放在另外一个临时表里.这样一来数据量会小很多,你就可以用SQL了. 并不
是真让你用 cursor 去一行一行地去处理.

【 在 olive2008 (olive) 的大作中提到: 】
: 用cursor的话都不需要self join了。从第一行开始给个行号为1,第二行如果
checksum
: 和上一行一样就还是1,不一样加 1,这样每次都是每两行进行比较,以此类推就行了
: 。然后按行号和checksum group就行了。
: 想找到不用cursor 的方法,因为表太大了。找不到就用cursor了,毕竟再多数据,也
: 只是简单的加1的操作。
: 我在解这个问题的时候就体会到,SQL server 的window function 缺少一个running
: ranking的功能,就是窗口分割好以
: 后,每个窗口里面逐行比较。类似running summary的功能。希望微软以后能加上。




--
※ 修改:·anmitbbsguy 於 Mar  4 12:55:14 2017 修改本文·[FROM: 147.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 147.]

 
matt1981
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 17 ]

发信人: matt1981 (害虫007), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sat Mar  4 14:30:33 2017, 美东)

看这次题目理解的正确吗?

代码见下图





--
※ 修改:·matt1981 於 Mar  5 07:32:55 2017 修改本文·[FROM: 2601:0196:4701:9]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2601:0196:4701:]

此主题相关图片如下:

[删除]

 
olive2008
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 18 ]

发信人: olive2008 (olive), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Sun Mar  5 12:55:41 2017, 美东)

这个是对的,谢谢。稍微改一下就是:

ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY Start) -
ROW_NUMBER() OVER(PARTITION BY GroupId, BinaryCheckSum ORDER BY Start) as rn.

【 在 matt1981 (害虫007) 的大作中提到: 】
: 看这次题目理解的正确吗?
: 代码见下图



--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 98.]

 
SmithCN
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 19 ]

发信人: SmithCN (史密斯), 信区: Database
标  题: Re: 求SQL 难题
发信站: BBS 未名空间站 (Wed Jan  2 15:00:11 2019, 美东)

test
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 135.]

[分页:1 ]
[快速返回] [ 进入数据库讨论区] [返回顶部]
回复文章
标题:
内 容:

未名交友
将您的链接放在这儿

友情链接


 

Site Map - Contact Us - Terms and Conditions - Privacy Policy

版权所有,未名空间(mitbbs.com),since 1996