M*******r
发帖数: 165 | 1 The following questions relate to a table below:
Stock_Ticker Analyst_Name Rating_Date Rating_Name
G Abramson 6/9/09 Hold
G Abramson 1/20/10 Sell
G Jones 12/5/09 Buy
Write an SQL query that would select the latest recommendation from each
analyst for each stock.
Write an SQL query that would select the latest recommendation from each
analyst for each stock as known on a specific date (e.g. 1/1/10).
Indicate problems that you see with this table design. How would you go
about amending this data structure? | g***l
发帖数: 18555 | 2 弄些名字，G都是一个STOCK吗。Abramson都是一个Abramson吗？请用ID好不好，
recommendation是什么,买还是卖啊？ | M*******r
发帖数: 165 | 3 G是股票；是一个人；recommand就是最后一个column
【在 g***l 的大作中提到】
: 弄些名字，G都是一个STOCK吗。Abramson都是一个Abramson吗？请用ID好不好，
: recommendation是什么,买还是卖啊？
| g***l
发帖数: 18555 | 4 TRANSACTION都没有ID,有一个人一天同一股票买了又卖怎么办，又没有时间？这个
TABLE就是POORLY DESIGN的
【在 M*******r 的大作中提到】
: G是股票；是一个人；recommand就是最后一个column
| M*******r
发帖数: 165 | 5 你没搞懂题目
【在 g***l 的大作中提到】
: TRANSACTION都没有ID,有一个人一天同一股票买了又卖怎么办，又没有时间？这个
: TABLE就是POORLY DESIGN的
| B*****g
发帖数: 34098 | 6 90%+的数据库版SQL问题可以用partition by解决
【在 M*******r 的大作中提到】
: The following questions relate to a table below:
: Stock_Ticker Analyst_Name Rating_Date Rating_Name
: G Abramson 6/9/09 Hold
: G Abramson 1/20/10 Sell
: G Jones 12/5/09 Buy
: Write an SQL query that would select the latest recommendation from each
: analyst for each stock.
: Write an SQL query that would select the latest recommendation from each
: analyst for each stock as known on a specific date (e.g. 1/1/10).
: Indicate problems that you see with this table design. How would you go
| j*****n
发帖数: 1781 | 7 agree
【在 B*****g 的大作中提到】
: 90%+的数据库版SQL问题可以用partition by解决
| g***l
发帖数: 18555 | 8 我哪里没搞懂，你不是问这个TABLE的设计有什么毛病么，对于DAY TRADER，一天买入卖
出同一只股票无数次的情况多的很，连个ID和TIME都没有，怎么区分？实际情况里你这
TABLE也就是用户显示一下，做数据分析是会让人笑话的。 | k*z
发帖数: 4704 | 9 --table设计的有问题，首先应该给员工分配ID,防止重名，其次给时间加timestamp防止一天多次评级。再有就是
--初学者，练习练习，还能优化么？ 应该不用分析函数，子函数也没必要。
drop table stock;
create table stock (
stock_ticket varchar2(5) ,
analyst_name varchar2(25),
rating_date date,
rating_name varchar2(10)
)
;
insert into stock values ('F','Tim','1-Dec-2010','Sell');
insert into stock values ('F','Tim','1-May-2009','Sell');
insert into stock values ('G','Abramson','6-Sep-2009','Hold');
insert into stock values ('G','Abramson','1-Oct-2010','Sell');
insert into stock values ('E','Abramson','1-Mar-2010','Sell');
insert into stock values ('G','Johns','5-Dec-2009','Buy');
insert into stock values ('F','Johns','5-Nov-2010','Buy');
insert into stock values ('E','Tim','1-Nov-2009','Sell');
insert into stock values ('G','Abramson','1-Dec-2010','Sell');
insert into stock values ('E','Tim','19-Feb-2010','Sell');
select * from stock;
select stock_ticket, analyst_name, max(rating_date) Recentest_Rating, rating_
name
from stock
group by stock_ticket, analyst_name, rating_name
order by stock_ticket,analyst_name; | M*******r
发帖数: 165 | 10 多谢高手指点！
防止一天多次评级。再有就是
【在 k*z 的大作中提到】
: --table设计的有问题，首先应该给员工分配ID,防止重名，其次给时间加timestamp防止一天多次评级。再有就是
: --初学者，练习练习，还能优化么？ 应该不用分析函数，子函数也没必要。
: drop table stock;
: create table stock (
: stock_ticket varchar2(5) ,
: analyst_name varchar2(25),
: rating_date date,
: rating_name varchar2(10)
: )
: ;
| | | k*z
发帖数: 4704 | 11 i am trying to learn SQL query and PLSQL now | c*****d
发帖数: 6045 | 12 神医要抢我们饭碗了
555
【在 k*z 的大作中提到】
: i am trying to learn SQL query and PLSQL now
| B*****g
发帖数: 34098 | 13 你的SQL是做这个用的吗？
Write an SQL query that would select the latest recommendation from each
analyst for each stock.
防止一天多次评级。再有就是
【在 k*z 的大作中提到】
: --table设计的有问题，首先应该给员工分配ID,防止重名，其次给时间加timestamp防止一天多次评级。再有就是
: --初学者，练习练习，还能优化么？ 应该不用分析函数，子函数也没必要。
: drop table stock;
: create table stock (
: stock_ticket varchar2(5) ,
: analyst_name varchar2(25),
: rating_date date,
: rating_name varchar2(10)
: )
: ;
| B*****g
发帖数: 34098 | 14 我们也可以抢神医饭碗
【在 c*****d 的大作中提到】
: 神医要抢我们饭碗了
: 555
| y****9
发帖数: 144 | 15 ------
select stock_ticket, analyst_name, max(rating_date) Recentest_Rating, rating_
name
from stock
group by stock_ticket, analyst_name, rating_name
order by stock_ticket,analyst_name;
-----
The above query probably is not what the OP means. It gave the following
results:
For each stock, for each rating_name, the latest time when an analyzst made
the recommedation.
for example:
insert into stock values ('G','Abramson','6-Sep-2008','Hold');
insert into stock values ('G','Abramson','6-Sep-2009','Hold');
insert into stock values ('G','Abramson','1-Oct-2010','Sell');
insert into stock values ('G','Abramson','1-Oct-2011','Sell');
G Abramson '6-Sep-2009' Hold
G Abramson '1-Oct-2011' Sell
I think partition by and analytical functin is the solution.
without checking syntax, just describe logic here: partition by stock and
analyst, order by date desc, generate rank or row_num, select out rank = 1 | k*z
发帖数: 4704 | 16 不是，我是选出每个股票 被所有分析员给出的最新评级
但是和第一个问题异曲同工啊。
他要的是所有的每一个股票，被所有的交易员评了N次，我们要选出最近的一次。
我上面给出这个答案了啊。
原题第二个问题有问题，如果日期给定了的话，那就没有先后了，所以可能需要
timestamp.
【在 B*****g 的大作中提到】
: 你的SQL是做这个用的吗？
: Write an SQL query that would select the latest recommendation from each
: analyst for each stock.
:
: 防止一天多次评级。再有就是
| B*****g
发帖数: 34098 | 17 再想想，看看楼上的帖子
【在 k*z 的大作中提到】
: 不是，我是选出每个股票 被所有分析员给出的最新评级
: 但是和第一个问题异曲同工啊。
: 他要的是所有的每一个股票，被所有的交易员评了N次，我们要选出最近的一次。
: 我上面给出这个答案了啊。
: 原题第二个问题有问题，如果日期给定了的话，那就没有先后了，所以可能需要
: timestamp.
| M*******r
发帖数: 165 | 18 附上我的解答，在mysql 5.1下测试过：
create table stock (
stock_ticket char(5) ,
analyst_name char(25),
rating_date date,
rating_name char(10)
)
;
insert into stock values ('G','Abramson','2009-06-09','Hold');
insert into stock values ('G','Abramson','2010-01-20','Sell');
insert into stock values ('G','Jones','2009-12-05','Buy');
select * from stock;
# for question 1
select stock_ticket, analyst_name, max(rating_date) rating_date, rating_name
from stock
group by analyst_name, stock_ticket;
# for question 2
select stock_ticket, analyst_name, max(rating_date) rating_date, rating_name
from stock
where rating_date < '2010-01-01'
group by stock_ticket, analyst_name
order by stock_ticket, analyst_name
; | k*z
发帖数: 4704 | 19 我上写的是错的，给出的是 每个股票，每个交易员，每种！！推荐的最近的日期。 我
们要的是这个人的最近日期的推荐，而不是每种推荐的最近日期。。。。
新的如下：
select stock_ticket, analyst_name, rating_name rating, rating_date from
stock
join (select max(rating_date) as rating_date,stock_ticket, analyst_name from
stock group by stock_ticket, analyst_name) stock2
using (rating_date,stock_ticket,analyst_name)
order by stock_ticket; | k*z
发帖数: 4704 | 20 再次请师傅们评价。
drop table stock;
create table stock (
stock_ticket varchar2(5) ,
analyst_name varchar2(25),
rating_date date,
rating_name varchar2(10)
)
;
insert into stock values ('F','Tim','1-Dec-2010','Sell');
insert into stock values ('F','Tim','1-May-2009','Sell');
insert into stock values ('G','Abramson','6-Sep-2009','Hold');
insert into stock values ('G','Abramson','1-Oct-2010','Sell');
insert into stock values ('E','Abramson','1-Mar-2010','Sell');
insert into stock values ('G','Johns','5-Dec-2009','Buy');
insert into stock values ('F','Johns','5-Nov-2010','Buy');
insert into stock values ('E','Tim','1-Nov-2009','Sell');
insert into stock values ('G','Abramson','1-Dec-2010','Sell');
insert into stock values ('E','Tim','19-Feb-2010','Sell');
select * from stock;
--选出每个股票，被每个交易员，两种推荐，和两种推荐的最近日期
select stock_ticket, analyst_name, max(rating_date) Recentest_Rating, rating_name
from stock
group by stock_ticket, analyst_name, rating_name
order by stock_ticket,analyst_name;
--选出每个股票，被每个交易员，给出的最近的日期的推荐， 同时列出日期
select stock_ticket, analyst_name, rating_name rating, rating_date from stock
join (select max(rating_date) as rating_date,stock_ticket, analyst_name from stock group by stock_ticket, analyst_name) stock2
using (rating_date,stock_ticket,analyst_name)
order by stock_ticket;
-- Partition Method on choosing the max or min
select stock_ticket, analyst_name, rating_name, rating_date
from (select stock_ticket, analyst_name, rating_name, rating_date, rank()
over (partition by stock_ticket,analyst_name order by rating_date desc) rn from stock)
stock2 where rn=1;
--选出每个股票，被每个交易员，给出的最近的日期的推荐， 同时列出日期
explain plan for select stock_ticket, analyst_name, rating_name rating, rating_date from stock
join (select max(rating_date) as rating_date,stock_ticket, analyst_name from stock group by stock_ticket, analyst_name) stock2
using (rating_date,stock_ticket,analyst_name)
order by stock_ticket;
select * from table(dbms_xplan.display);
-- Partition Method on choosing the max or min
explain plan for select stock_ticket, analyst_name, rating_name, rating_date
from (select stock_ticket, analyst_name, rating_name, rating_date, rank()
over (partition by stock_ticket,analyst_name order by rating_date desc) rn from stock)
stock2 where rn=1;
select * from table(dbms_xplan.display); | | | M*******r
发帖数: 165 | 21 阁下是用oracle？
【在 k*z 的大作中提到】
: 再次请师傅们评价。
: drop table stock;
: create table stock (
: stock_ticket varchar2(5) ,
: analyst_name varchar2(25),
: rating_date date,
: rating_name varchar2(10)
: )
: ;
: insert into stock values ('F','Tim','1-Dec-2010','Sell');
| B*****g
发帖数: 34098 | 22 sql server/db2 也行
【在 M*******r 的大作中提到】
: 阁下是用oracle？
| k*z
发帖数: 4704 | 23 老师给个评价啊。
【在 B*****g 的大作中提到】
: sql server/db2 也行
| j*****n
发帖数: 1781 | 24 dude, you got to learn financial things since you are joining a such firm.
http://www.nasdaq.com/quotes/analyst-recommendations.aspx
【在 g***l 的大作中提到】
: 弄些名字，G都是一个STOCK吗。Abramson都是一个Abramson吗？请用ID好不好，
: recommendation是什么,买还是卖啊？
| B*****g
发帖数: 34098 | 25 你开个讲座吧
【在 j*****n 的大作中提到】
: dude, you got to learn financial things since you are joining a such firm.
: http://www.nasdaq.com/quotes/analyst-recommendations.aspx
| g***l
发帖数: 18555 | 26 这么烂的TABLE，SOLUTION居然是DROP TABLE, RECREATE，你去人PRODUCTION DROP
TABLE啊，路子是越学越野了，MANIPULATE这种业余的TABLE，小心技术越搞越烂，LOL | s********y
发帖数: 122 | 27 Here are the answers (assuming the table is called stock):
--1--
select a.* from stock a,
( select b.Stock_Ticker, b.Analyst_Name, max(b.Rating_Date) as Rating_Date
from stock b
group by b.Stock_Ticker, b.Analyst_Name
) c
where a.Stock_Ticker = c.Stock_Ticker
and a.Analyst_Name = c.Analyst_Name
and a.Rating_Date = c.Rating_Date
--2--
select a.* from stock a,
( select b.Stock_Ticker, b.Analyst_Name, max(b.Rating_Date) as Rating_Date
from stock b where convert(char(10), b.Rating_Date, 120) = @yyyy-mm-dd
group by b.Stock_Ticker, b.Analyst_Name
) c
where a.Stock_Ticker = c.Stock_Ticker
and a.Analyst_Name = c.Analyst_Name
and a.Rating_Date = c.Rating_Date
The "where" clause in answer 2 effectively "select"s ALL rating times in a
specific day.
Above sql should give you the expected results...that is if I understand
your original questions correctly.
As far as table design goes, I think yours suffices as a simple interview question. It is normalized enough. You may want to introduce id's for analyst name, but that is only if you have other tables that also reference analyst or there can be name conflicts. But even though simply using a varchar analyst_name which also servers as an id (or key) is fine by me, albeit varchar is little inefficient. Of course you can introduce id's for rating name, or even ticker, but that will be an over kill in this specific problem domain. | B*****g
发帖数: 34098 | 28 不要欺负新同学
LOL
【在 g***l 的大作中提到】
: 这么烂的TABLE，SOLUTION居然是DROP TABLE, RECREATE，你去人PRODUCTION DROP
: TABLE啊，路子是越学越野了，MANIPULATE这种业余的TABLE，小心技术越搞越烂，LOL
| g***l
发帖数: 18555 | 29 我错了，呜呜。闪。
【在 B*****g 的大作中提到】
: 不要欺负新同学
:
: LOL
| s********y
发帖数: 122 | 30 Have not tried your answers, but they seem a bit complicated and less
intuitive. They may work though...really need to test it out to say for
sure as database sql can be really tricky sometime. Also, it is not generic
enough, as not all databases support syntax such as partition and rank.
【在 k*z 的大作中提到】
: 老师给个评价啊。
| | | s********y
发帖数: 122 | 31 Honestly I have not been following db technology for long time, nor have I
the slightest interest in doing so... :) must be a relatively new thing in
sybase as far as I can remember. I know SQL server and oracle support that.
On a separate note, doing "so called" analytics work directly in database is
a joke to start with...no offense.
【在 B*****g 的大作中提到】
: 不要欺负新同学
:
: LOL
| k*z
发帖数: 4704 | 32 大家应该多学学tom大叔，多些回答，少些评价。说句实话，我们这里能对数据库进行
评价的大牛，我认为还没有。 | k*z
发帖数: 4704 | 33 还是删除了，不好太意气用事，最近深圳严打，要是被人人肉了，就要被和谐了。
【在 B*****g 的大作中提到】
: 不要欺负新同学
:
: LOL
| y****w
发帖数: 3747 | 34 哪儿这么大火气啊，就看beijing又广告了oracle一把。谁评价都行，说错了也不要紧
，你去纠正了人家也就学到了。比较dbms实现其实很有意义。
【在 k*z 的大作中提到】
: 大家应该多学学tom大叔，多些回答，少些评价。说句实话，我们这里能对数据库进行
: 评价的大牛，我认为还没有。
| k*z
发帖数: 4704 | 35 我说得不是他。
【在 y****w 的大作中提到】
: 哪儿这么大火气啊，就看beijing又广告了oracle一把。谁评价都行，说错了也不要紧
: ，你去纠正了人家也就学到了。比较dbms实现其实很有意义。
|
|
