|
|---|
|
|
|
|
|
|
p****1
发帖数: 275 | 1 有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)
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. | p****1
发帖数: 275 | | a*********4
发帖数: 13 | 3 你的这两道题我都做过，当时agency联系我的，花了一下午做完，发回去，就再没消息
了 | p****1
发帖数: 275 | 4 Mind sharing your answer here? 大家共同进步 :)
【在 a*********4 的大作中提到】
: 你的这两道题我都做过，当时agency联系我的，花了一下午做完，发回去，就再没消息
: 了
| b**k
发帖数: 3563 | | g***c
发帖数: 11523 | 6 你不会做就直接说不会做
直接请教这里的大牛帮你做出来
还尼玛最优解
sql有个鸡毛最优解
【在 p****1 的大作中提到】
: Mind sharing your answer here? 大家共同进步 :)
| s*****r
发帖数: 43070 | 7 这根本不是SQL的题目，倒像是DW的构架，按照date进行sharding
或者搞个index table，只存loadid和timestamp，每个batch只有一行，join起来很容易
这个问题的层次好水 |
|
|
|
|
|
|
|
