我想在 group-by 查詢中獲取隨機值，如下所示：
SELECT city
, SOME_RANDOMIZER_FUNC(latitude) as "RANDOM_LATITUDE_IN_CITY"
, SOME_RANDOMIZER_FUNC(longitude) AS "RANDOM_LONGITUDE_IN_CITY"
FROM some_table
GROUP BY city
輸入：
city LATITUDE LONGITUDE
STO -31.3935 -57.9413
STO -31.0274 -57.8081
STO -30.7852 -57.7765
STO -30.4274 -56.4718
NDU -30.2747 -57.6023
NDU -32.2909 -58.0737
NDU -32.0286 -57.8468
NDU -32.3600 -57.2021
NDU -32.6816 -57.6541
MBO -31.7085 -55.9873
MBO -30.9359 -55.5457
MBO -31.1972 -55.7574
MBO -31.7711 -54.6904
所需的輸出：
city RANDOM_LATITUDE_IN_CITY RANDOM_LONGITUDE_IN_CITY
STO -31.0274 -57.9413
NDU -32.3600 -57.6541
MBO -30.9359 -55.5457
其中函式SOME_RANDOMIZER_FUNC回傳組內的隨機值。
uj5u.com熱心網友回復：
在隨機函式的輸入引數中，您呈現所有資料，然后對其應用分組，這是不允許的。最好的方法是使用以下代碼：
with s_table as (
select
city,
latitude,
longitude,
row_number() over(partition by city order by DBMS_RANDOM.VALUE) as random_sort
from
some_table
)
select
city,
latitude,
longitude
from
s_table
where
random_sort =1
uj5u.com熱心網友回復：
如果您想要一些非確定性的行，那么您可以嘗試any_value從 21c 開始記錄的函式：
create table t as select trunc((level - 1)/10) as city , level as lat , 100 - level as lon from dual connect by level < 101
select /* gather_plan_statistics*/ city , any_value(lat) as lat , any_value(lon) as lon from t group by city城市 | 緯度 | 倫敦 ---: | --: | ——： 0 | 1 | 99 1 | 11 | 89 2 | 21 | 79 3 | 31 | 69 4 | 41 | 59 5 | 51 | 49 6 | 61 | 39 7 | 71 | 29 8 | 81 | 19 9 | 91 | 9
db<>在這里擺弄
uj5u.com熱心網友回復：
您可以將 FIRST_VALUE 與 DBMS_RANDOM.VALUE 結合使用
例如：
SELECT city, latitude, longitude FROM ( SELECT city , FIRST_VALUE(latitude) OVER (PARTITION BY city ORDER BY DBMS_RANDOM.VALUE) as latitude , FIRST_VALUE(longitude) OVER (PARTITION BY city ORDER BY DBMS_RANDOM.VALUE) as longitude FROM some_table ) GROUP BY city, latitude, longitude ORDER BY city;城市 | 緯度 | 經度 :--- | -------: | --------： 管理層收購 | -30.9359 | -55.5457 北大 | -32.6816 | -57.6541 申通 | -30.4274 | -56.4718
關于db<>fiddle 的演示在這里
uj5u.com熱心網友回復：
每個城市隨機編號您的行，然后將編號保留為 1。
SELECT city, latitude, longitude
FROM
(
SELECT
city, latitude, longitude,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY DBMS_RANDOM.VALUE) AS rn
FROM some_table
)
WHERE rn = 1
ORDER BY city;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399498.html