将数据存储到 ntiles mysql 中

发布于 2024-12-17 16:47:18 字数 608 浏览 1 评论 0原文

我正在尝试在 mysql 中对数据进行标准分桶：将数据沿着某些特征拆分为 N 个桶，并将桶编号分配给每个条目。条目由日期、永久号和卷组成，如下所示：

dte、permno、交易量、day_rank
1986-03-07, 10071, 9203, 空

该表的标题为 bam。在每个日期（dte），我想根据数量对许多 permno（id）进行排名。然后我想将每个 permno、dte 的排名分配给 day_rank。因此，成功的查询将根据卷对每个 dte 的 permno 进行排名。我正在尝试使用这个复杂的查询来执行此操作：

 select a.dte, a.permno, count(b.volume) as rnk
 from bam a inner join bam b
 on a.dte = b.dte and (a.volume < b.volume or (a.permno=b.permno and a.volume =      b.volume))
 order by a.dte, a.volume;

这是有效的，但是它非常非常慢，对于一个大小合理的表来说，运行大约需要 1 天。

有什么想法吗？

原文

I am trying to do a standard bucketing of data in mysql: split the data along some characteristic into N buckets and assign the bucket number to each entry. The entries consist of date, permno and volume as follows:

dte, permno, volume, day_rank
1986-03-07, 10071, 9203, NULL

The table is titled bam. On each date (dte) there are many permnos (ids) that I would like to rank on the basis of volume. I would then like to assign the ranking for each permno, dte to day_rank. A successful query would thus rank the permnos for each dte based on volume. I am attempting to do this with this convoluted query:

 select a.dte, a.permno, count(b.volume) as rnk
 from bam a inner join bam b
 on a.dte = b.dte and (a.volume < b.volume or (a.permno=b.permno and a.volume =      b.volume))
 order by a.dte, a.volume;

This works however it is very very slow to the point where for a table that's reasonably sized it would take ~1 day to run.

Any ideas?

分享到QQ

分享到微博