p******d
发帖数: 1120 | 1 如何把下面的数据
ID Attr1 Attr2
1 A1 B1
1 A2 B2
1 A3 B3
2 A4 B4
3 A5 B5
3 A6 B6
3 A7 B7
3 A8 B8
4 A9 B9
4 A10 B10
变成这样的：
ID Attr1 Attr2 Attr1 Attr2 Attr1 Attr2 Attr1 Attr2
1 A1 B1 A2 B2 A3 B3
2 A4 B4
3 A5 B5 A6 B6 A7 B7 A8 B8
4 A9 B9 A10 B10
前提是不知道每个ID确切有多少行。
谢谢啦。 |
|
j*****n
发帖数: 1781 | 2 finally got some time...
select A.*
from table AS A
join (select attr1, min(attr2) as theOne
from table
group by attr1) AS B
ON A.attr1 = b.attr1 AND A.Attr2 = B.theOne |
|
o****i
发帖数: 5 | 3 hehe, 姑且从这里开始吧 :)
本来想先说一个Oracle的好处，再说一个坏处，现在就颠倒一下吧
As I know, they don't.
For example, outer joins.
SQL92's outer join is like this:
SELECT ......
FROM A LEFT/RIGHT/FULL OUTER JOIN B ON (A.attr1 = B.attr2)
.........
In Oracle, you can do LEFT, RIGHT outer join like this
SELECT .....
FROM A, B
WHERE A.attr1(+) = B.attr2
or A.attr1 = B.attr2(+) for RIGHT OUTER join.
But you don't have general means to do FULL OUTER JOIN:
|
|
z**k
发帖数: 378 | 4 大家帮忙
我的table大概是这样的
Attr1, Attr2, Attr3
A 1 1.1213
A 2 3.3242
A 3 2.123
B 4 8.123
B 5 3.1232
Attr1和Attr2都排好序了（分别都是增序，可以把Attr2看作是Index），我现在想对每
个Attr1取
第一个record，这样的SQL代码该怎么写啊 |
|
z**k
发帖数: 378 | 5 大家帮忙
我的table大概是这样的
Attr1, Attr2, Attr3
A 1 1.1213
A 2 3.3242
A 3 2.123
B 4 8.123
B 5 3.1232
Attr1和Attr2都排好序了（分别都是增序，可以把Attr2看作是Index），我现在想对每
个Attr1取
第一个record，这样的SQL代码该怎么写啊 |
|
k*******a
发帖数: 772 | 6 用两次transpose就可以了，只是变量顺序有点不同
data test;
input ID Attr1 $ Attr2 $;
datalines;
1 A1 B1
1 A2 B2
1 A3 B3
2 A4 B4
3 A5 B5
3 A6 B6
3 A7 B7
3 A8 B8
4 A9 B9
4 A10 B10
;
run;
proc transpose data=test prefix=attr1 out=test1(drop=_name_);
by ID;
var attr1;
run;
proc transpose data=test prefix=attr2 out=test2(drop=_name_);
by ID;
var attr2;
run;
data final;
merge test1 test2;
by ID;
run; |
|
x********o
发帖数: 31 | 7 yes, you can not use A.attr1(+)=B.attr2(+);
but to my understanding,you can get a outer join using:
select distinct a.attr1,b.attr2 from a,b ; |
|
w*****7
发帖数: 263 | 8 这位同学, 你那个query出来的max值对应的是哪个attr1呀? (如果没attr1的话) |
|
k*****u
发帖数: 1688 | 9 抛砖引玉，用一个很繁琐的办法写出来了。呵呵。不过凭感觉，我觉得应该有很简单的
办法。
data a;
input ID Attr1 $ Attr2 $;
cards;
1 A1 B1
1 A2 B2
1 A3 B3
2 A4 B4
3 A5 B5
3 A6 B6
3 A7 B7
3 A8 B8
4 A9 B9
4 A10 B10
;
run;
proc sort data=a;
by id;
run;
data a;
set a;
by id;
retain id2;
if first.id then id2=0;
id2=id2+1;
output;
run;
proc print data=a;
run;
proc sql;
create table temp1 as
select count(1) as cnt
from a
group by id;
select ... 阅读全帖 |
|
y********0
发帖数: 638 | 10 data one;
input ID Attr1 $ Attr2 $;
datalines;
1 A1 B1
1 A2 B2
1 A3 B3
2 A4 B4
3 A5 B5
3 A6 B6
3 A7 B7
3 A8 B8
4 A9 B9
4 A10 B10
;
run;
data two;
set one;
v1=attr1; output;
v1=attr2; output;
run;
proc transpose data=two out=three(drop=_name_);
var v1;
by id;
run; |
|
n*********e
发帖数: 318 | 11 #if done in Python
f = open('input.txt', 'r')
f.next()
d ={}
max_len = 0
for line in f:
line = line.strip()
ID, Attr1, Attr2 = line.split(' ')
if ID not in d:
d[ID] = []
d[ID].extend([Attr1,Attr2])
if max_len < len(d[ID]): max_len = len(d[ID])
o = open('output.txt','wb')
o.write('ID')
for i in xrange(max_len/2):
o.write('\tAttr1\tAttr2')
o.write('\n')
for ID in sorted(d):
o.write(ID +'\t' + '\t'.join(d[ID]) + '\n')
o.close() |
|
|
|
C******s
发帖数: 818 | 14
command=text&attr1=nail+art&attr2=&%2Fcom%2Fsephora%2Fsearch%2Fendeca%2Fui
%2FEndecaSearchFormHandler.search=search&_D%3A%2Fcom%2Fsephora%2Fsearch%2F
endeca%2Fui%2FEndecaSearchFormHandler.search=+&maxRecords=18&recordOffset=
0&seeAllCategory=false&searchString=nail+art&_DARGS=%2Fincludes%2FsearchFi
nder.jhtml
什么都没有呀 |
|
a9
发帖数: 21638 | 15 select * from table where attr2 in (select max(attr2) from table group by
attr1) |
|
p**e
发帖数: 11 | 16 select * from(
select *,
ROW_NUMBER() over(partition by attr1 order by attr2 asc) rn
from sample ) c
where rn<2; |
|
a9
发帖数: 21638 | 17 你这个和我那个有什么区别啊？
不就是多了个attr1列？ |
|
B*****g
发帖数: 34098 | 18 其实要考虑：
1.attr2是不是unique？
2.attr1，attr2 combine是不是unique？
3.几个column的type
4.index在哪？ |
|
h**o
发帖数: 548 | 19 大概几十台servers, 每台server每天分析几十GB公司自己格式的web log.
目前是用c 语言分析,结果存成每日的xml.
然后有一台management server, 每天从 这几十台servers 收集 xml结果，
再用 perl parse 这些 xml 并和已有的历史文件（也是xml）合并生成 一个新的历史
文件。
记录包括daily，weekly, monthly的各种信息。
现在问题是这个xml文件太大不好parse。 想问能否用sql从新设计？
statistics 包括:
userID_$attr1_$attr2_$attr3_$attr4,
url_$attr1_$attr2_$attr3
sessionID_$attr3_$attr4
...
其中
$attrX is variable with a value. e.g. $attr3 is 手机类型 whose value can be
userID, url, sessionID are long lists of str... 阅读全帖 |
|
n****f
发帖数: 905 | 20 这段没看懂：
收集的数据挺多得，但都还有关系。 如果用sql，把$attr1, $attr2, $attr3, $attr4
的各个values 排列组合一下大概几百张表吧。每张表就存诸如userID之类的一长串
string（目前大概几百万用户，以后可能增加）.一般是不是就是这样设计的?
几百表？ 不会吧？WHY？ |
|
h**o
发帖数: 548 | 21 谢谢大家。
对，瓶颈不在"分析web log."而在“把当天的xml和已有的历史文件（也是xml）
累积(是merge,不是简单的加)生成一个新的历史文件xml”这步。
前一年还好，现在客户多了，访问的量大了。累积量也大了，
历史文件 里 userID， url等 好多。没法parse了。
xml设计不是我做的。但现在要我重新设计。
客户需求类似于OLAP。
举个例子: userID_$attr1_$attr2_$attr3_$attr4：
$attr１ 包括 上传｜下传
$attr2 包括 各种 各种手机电脑类型 包括 爱风 | 安猪｜...
$attr3 包括 用户的 content 类型 包括 图型 | 文本 | 视频 | ...
$attr4 包括 公司的东西。 attr4A | attr4B | attr4C | attr4D | ...
例如 monthly report of userID_下传_爱风_视频_attr4A 目的是求：
每月有多少爱风手机用户下传attr4A的视频。注意每个符合要求的用户string都要存在
这张report里，否则将来merge时没法知道... 阅读全帖 |
|
M***0
发帖数: 1180 | 22 select * from xxx where attr2 in
(select min(attr2) from xxx group by attr1) |
|
w*******y
发帖数: 60932 | 23 Use code UPS2Day, and select UPS 2 day shipping to get free shipping.
You also get three free samples with every order.
Link:
http://www.sephora.com/browse/product.jhtml?id=P274719
Please note that this is $15 for 0.25 deluxe size jar. The original size is
$150 for 1 oz!
Instead of paying $150 for one original size, purchase 4 of the small ones.
You will end up paying $60 instead of $150.
A 0.14oz eyecream is also included with the face cream, if purchased
separately, it will cost $95.00 for 0.5... 阅读全帖 |
|
w*******y
发帖数: 60932 | 24 I have seen a few of the The North Face Men's Khumbu Jacket deals pop up
over the last few days. Problem was the sizes were very limited and many
did not have the greatest color choices. I was able to find some different
color schemes for less at bobwards.com. I picked up the Sullivan Green
North Face Fleece/Jacket for $49.29. The jacket I chose was $48 and I threw
in some hand warmers to get the free shipping. (all orders over $49 get
free shipping!)
Enjoy!
Men's Khumbu Fleece Jackets:
htt... 阅读全帖 |
|
