将表中的每个字段与同一表中的其他每个字段进行比较

Question

想象一个只有一列的表。

+------+
|  v   |
+------+
|0.1234|
|0.8923|
|0.5221|
+------+

我想为k：

行做以下操作

1. 采取行k = 1值：0.1234
2. 计数表的其余部分中有多少个值小于或等于第1行中的值。

遍历所有行输出应为：

+------+-------+
| v    |output |
+------+-------+
|0.1234|   0   |
|0.8923|   2   | 
|0.5221|   1   | 
+------+-------+

快速更新我正在使用这种方法来计算上表中V的每个值的统计量。对于我处理的数据大小而言，交叉联接方法太慢了。因此，相反，我为V值的网格计算了我的统计数据，然后将它们与原始数据中的VS匹配。v_table是从之前的数据表，Stat_comp是统计表。

AS SELECT t1.* 
,CASE WHEN v<=1.000000 THEN pr_1 
WHEN v<=2.000000 AND v>1.000000 THEN pr_2 
FROM v_table  AS t1 
LEFT OUTER JOIN stat_comp AS t2

Answer 1

Windows函数已于1999年添加到ANSI/ISO SQL中，并在2013年5月15日发布的版本0.11中将其添加到Hive。您正在寻找的是的变体，排名高，在ANSI/ISO SQL中：2011看起来像这样 -

rank () over (order by v with ties high) - 1

Hive当前不支持with ties ...，但是可以使用count(*) over (...)

实现逻辑

---

select  v
       ,count(*) over (order by v) - 1 as rank_with_ties_high_implicit
from    mytable
;

或

select  v
       ,count(*) over 
        (
            order by v
            range between unbounded preceding and current row            
        )  - 1  as rank_with_ties_high_explicit
from    mytable
;

Answer 2

生成样本数据

select 0.1234 as v into #t
union all
select 0.8923
union all
select 0.5221

这是查询

;with ct as (
    select ROW_NUMBER() over (order by v) rn
    , v
    from #t ot
)
select distinct v, a.cnt
from ct ot
    outer apply (select count(*) cnt from ct where ct.rn <> ot.rn and v <= ot.v) a

Answer 3

看到您的编辑后，看起来确实看起来可以使用笛卡尔产品，即CROSS JOIN。我打电话给您的桌子foo，并以bar：

的形式交叉加入它。

SELECT foo.v, COUNT(foo.v) - 1 AS output
FROM foo
CROSS JOIN foo bar
WHERE foo.v >= bar.v
GROUP BY foo.v;

这是小提琴。

此查询交叉加入列，使列的元素的每个排列都会返回(您可以通过删除SUM和GROUP BY条款并将bar.v添加到SELECT(。然后，当foo.v >= bar.v产生最终结果时，它添加了一个计数。

Answer 4

您可以用自己拿出桌子的完整笛卡尔产品，并总结一个案例语句：

select a.x
, sum(case when b.x < a.x then 1 else 0 end) as count_less_than_x
from (select distinct x from T) a
, T b
group by a.x

这将为您在表中的唯一值提供一行，而非唯一行的数量小于此值。

请注意，既没有加入也不是一个子句。在这种情况下，我们实际上想要。对于a的每一行，我们得到一个完整的副本别名为b。然后，我们可以检查每个人是否小于a.x。如果是这样，我们将1添加到计数中。如果没有，我们只添加0。