SQL, recruiter发过来的面试题 (转载) - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL, recruiter发过来的面试题 (转载)

SQL, recruiter发过来的面试题 (转载)# Database - 数据库

c*y2016-09-09 07:09

1 楼

世界杯首场，设东道主球队球衣( 南非)0伪币限时12小时.

r*u2016-09-09 07:09

2 楼

上千万，上亿的东西朋友同事之间就这么转来转去
那要偷，要换也太容易了

l*Q2016-09-09 07:09

3 楼

【以下文字转载自 Dreamer 讨论区】
发信人: Dreamer (不要问我从哪里来), 信区: Dreamer
标题: MM浴后未穿衣服的后果
发信站: BBS 未名空间站 (Thu Mar 20 13:08:13 2008)
MM浴后未穿衣服，一小狗跟来跟去
MM心想：不就是没穿衣服吗，用得着跟来跟去吗？
小狗心想：不就是两个肉包子吗，用得着挂那么高吗？

p*12016-09-09 07:09

4 楼

【以下文字转载自 JobHunting 讨论区】
发信人: peace1 (peace1), 信区: JobHunting
标题: SQL, recruiter发过来的面试题
发信站: BBS 未名空间站 (Thu Sep 8 22:14:20 2016, 美东)
有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
We work with large datasets, and are always performance conscious since
extended processing times will impact our time to market. Keep this in mind
as you answer the following questions:
There is a table defined as:
CREATE TABLE [Positions](
[load_id] [int] NOT NULL,
[acct_cd] [varchar](20) NOT NULL,
[acct_num] [varchar](255) NULL,
[sec_id] [varchar](50) NOT NULL,
[long_sht_cd] [varchar](3) NOT NULL,
[sedol] [varchar](15) NULL,
[isin] [varchar](15) NULL,
[cusip] [varchar](9) NULL,
[sec_type] [varchar](8) NULL,
[sec_name] [varchar](100) NULL,
[currency_cd] [varchar](3) NULL,
[total_holding] [decimal](18, 4) NULL,
[mkt_price] [float] NULL,
[datetime_stamp] [datetime] NULL,
CONSTRAINT [pk_Positions] PRIMARY KEY CLUSTERED (
[load_id] ASC,
[acct_cd] ASC,
[sec_id] ASC,
[long_sht_cd] ASC )
)
This table holds account positions data that are appended to multiple times
a day
There are currently some 24 million rows in the table. Every time we append
additional positions we add approximately 32,000 entries to this table, and
all 32,000 entries will have the same load_id. The load_id is incremented by
one each time we load a batch of 32,000 entries (i.e. the first 32K entries
have load_id=1, the next 32K has load_id=2, etc...). The datetime_stamp
field shows the time at which the entries were loaded and is the same for
all 32K entries in a single load.
How would you efficiently retrieve the first set of positions for the
current day given the above table definition?
Example:
Today, positions were loaded into this table at 8am, 10am and 3pm. At 5pm
today we want to know what positions were loaded at 8am since that is the
first load that occurred today. Note that for any given day, there can be
different number of loads and the times that the loads occur will vary.

e*n2016-09-09 07:09

5 楼

that's why it is called "雾里看花"

n*f2016-09-09 07:09

6 楼

很有意思的考題。
就針對單一表來說。我第一步，要建立為 TIMESTAMP 索引。
第二步，然後找出今天 TIMESTAMP 的第一個 LOAD_ID。
SELECT * FROM Positions WHERE LOAD_ID = (HERE YOUR SQL)
但是，在現實當中，這類事情不應該是這樣設計的。
DEVELOPER 要是這樣幹，肯定是不行的。
應該要有一個 LOAD 的 TABLE。裡面有
LOAD—ID （PK）
TIMESTAMP （DATETIME）
這樣，用戶要是要得到今天的 LOADID ，簡單查這個表就 OK 了。
有什麼原因要在 POSITION 表裡面加一個重複成千上萬次的TIMESTAMP COLUMN? 查詢
的代價太大。
這個考題，對於初學是能回答出結果，但是很難提出改進意見。
因此說，這不是考你 SQL ，是考你實戰經驗。
正確回答是，給出 SQL ，但是指出這樣設計的缺欠，並提供修正意見

p*12016-09-09 07:09

7 楼

Thanks very mu h for your reply.
Would appreciate if you could let me know how to do below?
就針對單一表來說。我第一步，要建立為 TIMESTAMP 索引。
第二步，然後找出今天 TIMESTAMP 的第一個 LOAD_ID。

【在 n****f 的大作中提到】

: 很有意思的考題。
: 就針對單一表來說。我第一步，要建立為 TIMESTAMP 索引。
: 第二步，然後找出今天 TIMESTAMP 的第一個 LOAD_ID。
: SELECT * FROM Positions WHERE LOAD_ID = (HERE YOUR SQL)
: 但是，在現實當中，這類事情不應該是這樣設計的。
: DEVELOPER 要是這樣幹，肯定是不行的。
: 應該要有一個 LOAD 的 TABLE。裡面有
: LOAD—ID （PK）
: TIMESTAMP （DATETIME）
: 這樣，用戶要是要得到今天的 LOADID ，簡單查這個表就 OK 了。

n*f2016-09-09 07:09

8 楼

In Oracle, it will be:
1. Create Index:
CREATE INDEX INDX_TIMESTAMP ON POSITIONS
(TO_CHAR (DATETIME_STAMP,'YYYY_MM_DD'));
2. Query Data:
SELECT *
FROM POSITIONS
WHERE ID =
(SELECT MIN (LOAD_ID)
FROM POSITIONS
WHERE TO_CHAR (DATETIME_STAMP, 'YYYY_MM_DD') =
TO_CHAR (SYSDATE, 'YYYY_MM_DD'))

p*12016-09-09 07:09

9 楼

Thank you very much!

【在 n****f 的大作中提到】

: In Oracle, it will be:
: 1. Create Index:
: CREATE INDEX INDX_TIMESTAMP ON POSITIONS
: (TO_CHAR (DATETIME_STAMP,'YYYY_MM_DD'));
: 2. Query Data:
: SELECT *
: FROM POSITIONS
: WHERE ID =
: (SELECT MIN (LOAD_ID)
: FROM POSITIONS

s*o2016-09-09 07:09

10 楼

如果是我，我就把出题的训一顿。这么多数据，成年累月的往一个TABLE里堆，
一看就是不懂行的，这种TABLE要么有HISTORY/ARCHIVE TABLE或者是
TABLE PARTITION，否则你REBUILD INDEX会成NIGHTMARE。你真的
每天都需要10年前的POSITION吗？

g*y2016-09-09 07:09

11 楼

Further optimization
1. create composite index on timestamp and load_id.
create index idx_pos on (DATETIME_STAMP,load_id);
2. Remove functional filter against indexed column.
select * from positions where load_id =
(select min(load_id) from positions where datetime_stamp >=trunc(sysdate));

【在 n****f 的大作中提到】

w*r2016-09-09 07:09

12 楼

select * from
(select
rank() over(partition by cast(datetime_stamp as date),load_id ) as by_day_
seq
a.*
from table a
where cast(datetime_stamp as date) = current_date ) a
where a.by_day_seq =1

c*l2016-09-09 07:09

13 楼

Oracle有clustered PK吗？

【在 n****f 的大作中提到】

p*12016-09-09 07:09

14 楼

will it be faster if we add clustered index to datetime_stamp first?

【在 w*r 的大作中提到】

: select * from
: (select
: rank() over(partition by cast(datetime_stamp as date),load_id ) as by_day_
: seq
: a.*
: from table a
: where cast(datetime_stamp as date) = current_date ) a
: where a.by_day_seq =1

n*f2016-09-09 07:09

15 楼

You should understand what is the "Clustered" mean.
then you will have the answer.
http://sql-plsql.blogspot.com/2010/06/non-clustered-indexes.html

【在 p****1 的大作中提到】

: will it be faster if we add clustered index to datetime_stamp first?

p*12016-09-09 07:09

16 楼

非常感谢。
One table can only have one clustered index, and the table has Primary Key
Clustered already, so, I can only add non clustered index to datetime_stamp.
:)

【在 n****f 的大作中提到】

: You should understand what is the "Clustered" mean.
: then you will have the answer.
: http://sql-plsql.blogspot.com/2010/06/non-clustered-indexes.html

n*f2016-09-09 07:09

17 楼

Please check DOC from Oracle:
http://docs.oracle.com/database/121/TGSQL/tgsql_indc.htm#TGSQL850
And you will find out when you should use the clustered index.

stamp.

【在 p****1 的大作中提到】

: 非常感谢。
: One table can only have one clustered index, and the table has Primary Key
: Clustered already, so, I can only add non clustered index to datetime_stamp.
: :)