算法求助

显示全部楼层 · 2010-3-1 11:08:27

sql算法求助

需求：统计航班信息有进无出次数和有出无进次数，即按时间排序，每2条记录做比较，如果相邻的2条记录的hblb相同，则记录一次有出无进或记录一次有进无出
数据如下：
sfzhhbrqhblb（1代表进港，2代表出港）

1 2011-1-3
1
1 2011-1-4
1
1 2011-1-5
2
1 2011-1-6
2
1 2011-1-7
1
1 2011-1-8
2
1
2011-1-9
1
1 2011-1-10
1
1 2011-1-11
1

上述数据得到的结果应该为：第一条和第二条的的flag都为1，则有进无出算一次，第二条和第三条不同，则不计算，第三条和第四条都为2，则有出无进算一次，
第四条和第五条不同，则不计算，第五条和第六条不同，则不计算，第六条和第七条不同，则不计算，第七条和第八条都为1，则有出无进加1，第八条和第九条都为1，则有进无出加1。
得到的结果为，身份证号为1的人的航班信息为：有进无出3次，有出无进1次。
ps：用PL/SQL已经实现，现在要求用sql实现，请问如何实现？

千问 · 2010-3-1 11:08:27

lag+over

千问 · 2010-3-1 11:08:27

具体如下：
SQL> select * from test;
SFZH HBRQHBLB
------ ----------- ----
12011-01-031
12011-01-041
12011-01-052
12011-01-062
12011-01-071
12011-01-082
12011-01-091
12011-01-101
12011-01-111
22011-01-092
22011-01-112
22011-01-132
22011-02-111
22011-03-131
22011-03-151
22011-03-251
16 rows selected
SQL>
SQL> selectsfzh,
2
sum(decode(hblb, '1', cnt - 1, 0)) flag1, --有进无出总次数
3
sum(decode(hblb, '2', cnt - 1, 0)) flag2 ---有出无进总次数
4from
5(select sfzh,
6
max(hblb) hblb,
7
count(*) cnt
8from
9(select sfzh,
10
hbrq,
11
hblb,
12
sum(rn) over(partition by sfzh order by hbrq) rn
13from
14(select sfzh,
15
hbrq,
16
hblb,
17
decode(hblb,
18
lag(hblb) over(partition by sfzh order by hbrq),
19
0,
20
row_number() over(partition by sfzh order by hbrq)) rn
21from test))
22group by sfzh, rn
23having count(*) > 1)
24group by sfzh;
SFZHFLAG1FLAG2
------ ---------- ----------
1
3
1
2
3
2
SQL>

千问 · 2010-3-1 11:08:27

恩，可以实现。谢谢

千问 · 2010-3-1 11:08:27

SQL> select sfzh,hblb,count(*)
2from (
3select sfzh,
4
hbrq,
5
hblb,
6
lag(hblb) over(partition by sfzh order by hbrq) lagval
7from SQL_TEST_TEMP
8)
9where hblb=lagval
10 group by sfzh,hblb ;
SFZH HBLB COUNT(*)
---------- ---------- ----------
1 1
3
1 2
1
这样简单点吧

这样应该简单点

千问 · 2010-3-1 11:08:27

原帖由 dingjun123 于 2011-1-12 15:49 发表
SQL> select sfzh,hblb,count(*)
2from (
3select sfzh,
4
hbrq,
5
hblb,
6
lag(hblb) over(partition by sfzh order by hbrq) lagval
7from SQL_TEST_TEMP
8)
9where hblb=lagval
10 group by sfzh,hblb ;
SFZH HBLB COUNT(*)
---------- ---------- ----------
1 1
3
1 2
1
这样简单点吧

这样应该简单点

好像有问题，如果时间是如下的：
SQL> select * from test;
SFZH HBRQHBLB
------ ----------- ----
12011-01-031
12011-01-041
12011-01-052
12011-01-062
12011-01-071
12011-01-082
12011-01-091
12011-01-101
12011-01-111
22011-01-092
22011-01-112
22011-01-132
22011-02-111
22011-03-131
22011-03-151
22011-03-251
16 rows selected
SQL>
--你语句得到的答案是如下：
SQL> select sfzh,hblb,count(*)
2from (
3select sfzh,
4
hbrq,
5
hblb,
6
lag(hblb) over(partition by sfzh order by hbrq) lagval
7
from test
8 )
9where hblb=lagval
10 group by sfzh,hblb ;
SFZH HBLB COUNT(*)
------ ---- ----------
11
3
22
2
21
3
12
1
SQL>

其实按照LZ的要求，应该得到：
flag1表示：有进无出总次数
flag2表示：有出无进总次数

SFZHFLAG1FLAG2
------ ---------- ----------
1
3
1
2
3
2

千问 · 2010-3-1 11:08:27

原帖由 bell6248 于 2011-1-12 16:03 发表

好像有问题，如果时间是如下的：
SQL> select * from test;
SFZH HBRQHBLB
------ ----------- ----
12011-01-031
12011-01-041
12011-01-052
12011-01-062
12011-01-071
12011-01-082
12011-01-091
12011-01-101
12011-01-111
22011-01-092
22011-01-112
22011-01-132
22011-02-111
22011-03-131
22011-03-151
22011-03-251
16 rows selected
SQL>
--你语句得到的答案是如下：
SQL> select sfzh,hblb,count(*)
2from (
3select sfzh,
4
hbrq,
5
hblb,
6
lag(hblb) over(partition by sfzh order by hbrq) lagval
7
from test
8 )
9where hblb=lagval
10 group by sfzh,hblb ;
SFZH HBLB COUNT(*)
------ ---- ----------
11
3
22
2
21
3
12
1
SQL>

其实按照LZ的要求，应该得到：
flag1表示：有进无出总次数
flag2表示：有出无进总次数

SFZHFLAG1FLAG2
------ ---------- ----------
1
3
1
2
3
2

呵呵，也就是改成decode就可以了，行列转换嘛

千问 · 2010-3-1 11:08:27

dingjun123的语句该一改就可以了！
SQL> select * from test;
SFZH HBRQHBLB
------ ----------- ----
12011-01-031
12011-01-041
12011-01-052
12011-01-062
12011-01-071
12011-01-082
12011-01-091
12011-01-101
12011-01-111
22011-01-092
22011-01-112
22011-01-132
22011-02-111
22011-03-131
22011-03-151
22011-03-251
16 rows selected
SQL>
SQL> select sfzh,
2 sum(decode(hblb, '1', cnt, 0)) flag1,
3 sum(decode(hblb, '2', cnt, 0)) flag2
4from
5(select sfzh, hblb, count(*) cnt
6from (select sfzh,
7
hbrq,
8
hblb,
9
lag(hblb) over(partition by sfzh order by hbrq) lagval
10
from test)
11 where hblb = lagval
12 group by sfzh, hblb)
13 group by sfzh;
SFZHFLAG1FLAG2
------ ---------- ----------
1
3
1
2
3
2

SQL>

千问 · 2010-3-1 11:08:27

原帖由 bell6248 于 2011-1-12 16:11 发表

dingjun123的语句该一改就可以了！
SQL> select * from test;
SFZH HBRQHBLB
------ ----------- ----
12011-01-031
12011-01-041
12011-01-052
12011-01-062
12011-01-071
12011-01-082
12011-01-091
12011-01-101
12011-01-111
22011-01-092
22011-01-112
22011-01-132
22011-02-111
22011-03-131
22011-03-151
22011-03-251
16 rows selected
SQL>
SQL> select sfzh,
2 sum(decode(hblb, '1', cnt, 0)) flag1,
3 sum(decode(hblb, '2', cnt, 0)) flag2
4from
5(select sfzh, hblb, count(*) cnt
6from (select sfzh,
7
hbrq,
8
hblb,
9
lag(hblb) over(partition by sfzh order by hbrq) lagval
10
from test)
11 where hblb = lagval
12 group by sfzh, hblb)
13 group by sfzh;
SFZHFLAG1FLAG2
------ ---------- ----------
1
3
1
2
3
2

SQL>

，中间那个group by sfzh, hblb不需要的吧，直接decode就行了
[ 本帖最后由 dingjun123 于 2011-1-12 16:17 编辑 ]

千问 · 2010-3-1 11:08:27

不错，今天又学到了新知识,oracle Lag和Lead函数的用法