n********6
发帖数: 1511 | 1 The original question is for SAS developers. However, I think the problem
can be solved by using SQL in database. I am working on that now for fun. |
h******l
发帖数: 422 | 2 first of all you will need an unique ID for each row added to make this
possible. For example:
Table: TEST1
id cn charge |
j*****n
发帖数: 1781 | 3 It is a good solution.
btw, try CTE with ROW_NUMBER() in 2k5 and 2k8. Same way to solve problem but
looks fancier.
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
|
B*****g
发帖数: 34098 | 4 1. 显然要pk
2. 显然要timestamp，要是先负后正的不能删，比如
Card_Number Charge Date
A123 -14.56 2008/01/01
A123 15.23 2008/01/02
A123 14.56 2008/01/03
那个A123都不能取消
3. 如果负的不多应该用loop一个一个找
4. 不过大家还是喜欢拽sql，咱也写一个，没测试，oracle。 |
B*****g
发帖数: 34098 | 5 这套数据上面的sql work吗？
Card_Number Charge
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
but
【在 j*****n 的大作中提到】
: It is a good solution.
: btw, try CTE with ROW_NUMBER() in 2k5 and 2k8. Same way to solve problem but
: looks fancier.
|
j*****n
发帖数: 1781 | 6 Yes, it works. I also tried adding more cases and seems fine.
btw, his code is added into my knowledge base, so I wouldn't have headache
in the future... just like your RANK()... hehe...
【在 B*****g 的大作中提到】
: 这套数据上面的sql work吗？
: Card_Number Charge
: A123 15.23
: A123 -14.56
: A234 11.12
: A234 3.87
: A234 11.12
: A234 -11.12
: A234 4.86
:
|
B*****g
发帖数: 34098 | 7 我看错了。你为啥不存我的？哈哈
【在 j*****n 的大作中提到】
: Yes, it works. I also tried adding more cases and seems fine.
: btw, his code is added into my knowledge base, so I wouldn't have headache
: in the future... just like your RANK()... hehe...
|
n********6
发帖数: 1511 | 8 I worked out a SAS version where SQL is extensively used and can be easily
converted into database. Next step is to see the difference in SAS solution
and database solution.
/* LOAD IN DATA */
DATA CARD;
INPUT Card_Number $ Charge;
CARDS;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
;
RUN;
/* CREATE TEMP TABLE FOR DISTINCT CARD_NUMBER, ADD NEW COLUMN AS ID */
PROC SQL;
CREATE TABLE CARD_NUM_STAGE AS
SELECT Card_Number, Count(Card_Number) AS Card_PerNum_ |
n********6
发帖数: 1511 | 9 厉害啊。我一开始就想用join来做，因为join速度最快。可是没搞出来。
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
|
n********6
发帖数: 1511 | 10 能解释一下吗？
1，2：z的where clause写在select clause里面，起什么作用呢？
3：t alias 派什么作用？
select * from @TEST1 y
where id not in
(select id
from (select
(select top 1 a.id from TEST1
left outer join @TEST1 b on a.cn = b.cn
and a.charge = (b.charge * -1)
and a.charge > 0
where z.cn = a.cn /*1*/
and a.charge = -z.charge /*2*/
) as id
from @TEST1 z
)t /*3*/
where id i
【在 h******l 的大作中提到】
: first of all you will need an unique ID for each row added to make this
: possible. For example:
: Table: TEST1
: id cn charge
|
|
|
n********6
发帖数: 1511 | 11 SOLUTION 2
PROC SQL;
CREATE TABLE CARD_OUTPUT AS
SELECT * FROM (SELECT * FROM CARD A WHERE A.CHARGE>0)
EXCEPT ALL (SELECT CARD_NUMBER, ABS(CHARGE) FROM CARD B WHERE B.CHARGE < 0);
QUIT; |
h******l
发帖数: 422 | 12 z在这里起到partition by的作用, 如果你用 RANK()的话.
t alias的作用是给temp table的.
【在 n********6 的大作中提到】
: 能解释一下吗？
: 1，2：z的where clause写在select clause里面，起什么作用呢？
: 3：t alias 派什么作用？
: select * from @TEST1 y
: where id not in
: (select id
: from (select
: (select top 1 a.id from TEST1
: left outer join @TEST1 b on a.cn = b.cn
: and a.charge = (b.charge * -1)
|
h******l
发帖数: 422 | 13 SQL SERVER没有except all :(
这样很简单.
);
【在 n********6 的大作中提到】
: SOLUTION 2
: PROC SQL;
: CREATE TABLE CARD_OUTPUT AS
: SELECT * FROM (SELECT * FROM CARD A WHERE A.CHARGE>0)
: EXCEPT ALL (SELECT CARD_NUMBER, ABS(CHARGE) FROM CARD B WHERE B.CHARGE < 0);
: QUIT;
|
n********6
发帖数: 1511 | 14 sql server and oracle都有实现类似功能的语句，似乎是except, minus, 或其他。
【在 h******l 的大作中提到】
: SQL SERVER没有except all :(
: 这样很简单.
:
: );
|
h******l
发帖数: 422 | 15 有EXCEPT但是没有EXCEPT ALL. 这样会受到很多的限制.
【在 n********6 的大作中提到】
: sql server and oracle都有实现类似功能的语句，似乎是except, minus, 或其他。
|
n********6
发帖数: 1511 | 16 刚查sql server 2005 books online,
有except/intersect
没有all option
【在 h******l 的大作中提到】
: 有EXCEPT但是没有EXCEPT ALL. 这样会受到很多的限制.
|
