sql - 按值选择行并计算回转半径

Question

我是 sql/presto 的新手，我正在尝试从这样的表开始计算所有用户 ID的回转半径：

Table
+----------+---------+-------+
|  userID  |     X   |   Y   |  
+----------+---------+-------+
|  asdasd  |    23   |  45   |       
|  sfasdf  |    11   |  12   |    
|  dfadsg  |    93   |  34   |  
|  asdasd  |    17   |  21   |   
|  dfasdf  |    12   |  43   |  
|  ...     |    ..   |  ...  | 
+----------+---------+-------+

我想返回一个包含所有用户 ID 及其回转半径的表格。

我能够恢复计算 100 点随机样本的回转半径(rog) 的代码，但我很难将它应用到我的桌子上。

with n as (select 100 as n)
--generate a set of points using random XY
, xy as (select SEQUENCE(1,n) as gid, st_point(random(), random()) as geom_point from n)
--distance between each point and centroid
, d as (select gid, st_distance(geom_point, geom_centroid) as dist from xy, c)
--standard deviation of distance
, d_sd as (select stddev(dist) from d)
--square the distance
, d2 as (select gid, dist, dist^2 as dist_squared from d)
--sum the squares and divide by n 
, d2sum as (select sum(dist_squared) as dist_squared_sum from d2)
--divide by n
, d2sum_normalize as (select dist_squared_sum / n as dist_norm from d2sum, n)
--square root
, rog as (select sqrt(dist_norm) as radius_of_gyration from d2sum_normalize)
--report radius of gyration and stddev(distance)
select * from rog, d_sd

谢谢您的帮助！

标签： sqlpostgispresto