PostgreSQL实用示例

PostgreSQL实用示例

• 参考PostgreSQL
• 参考pass

-- 创建表
CREATE TABLE "bd_peak_index_song_feature_lib" (
  "id" int8 NOT NULL,
	"features_l"  decimal[] NOT NULL,
  CONSTRAINT "bd_peak_index_song_feature_lib_pkey" PRIMARY KEY ("id")
);

-- 插入数据
INSERT INTO bd_peak_index_song_feature_lib
    VALUES (1,
		'{1,1,1}'
);

-- 查询数据
SELECT features_l FROM bd_peak_index_song_feature_lib WHERE id = 1;

-- 更新数据
UPDATE bd_peak_index_song_feature_lib SET features_l = '{2,2,2}'
    WHERE id = 1;
UPDATE bd_peak_index_song_feature_lib SET features_l = '{3,3,3}'
    WHERE id = 2;
UPDATE bd_peak_index_song_feature_lib SET features_l = '{4,4,4}'
    WHERE id = 3;
UPDATE bd_peak_index_song_feature_lib SET features_l = '{5,5,5}'
    WHERE id = 4;
UPDATE bd_peak_index_song_feature_lib SET features_l = '{6,6,6}'
    WHERE id = 5;		

-- 创建插件
CREATE EXTENSION pase;

-- 计算欧氏距离示例（Array）
SELECT sqrt(ARRAY[1,1]::numeric[] <?> pase(ARRAY[3,3]::numeric[], 0, 0)) AS distance;
-- 计算欧氏距离示例（字符串）
SELECT ARRAY[2, 1, 1]::float4[] <?> '5,1,1'::pase AS distance;

-- 计算特征库所有向量与指定向量的欧氏距离
SELECT id, sqrt(features_l::numeric[] <?> '1,1,1'::pase) AS distance FROM bd_peak_index_song_feature_lib ORDER BY distance;

-- 计算某一条向量与指定向量的欧氏距离
SELECT sqrt(Array[1,1,1]::numeric[] <?> '0.11255084723234177,-0.032232433557510376,0.07658641785383224'::pase) as distance from bd_peak_index_song_feature_lib order by distance desc limit 1;

-- 查询欧氏距离 余弦相似度 以及余弦相似度归一化之后的值
SELECT t_b.id,t_a.id
 ,
 SQRT ( t_b.features :: float4 [] <?> pase(t_a.features::float4[], 0, 0)) AS o_d,
 t_b.features :: float4 [] <?> pase(t_a.features::float4[], 0, 1) AS cos_d,
 (t_b.features :: float4 [] <?> pase(t_a.features::float4[], 0, 1))*0.5 + 0.5 AS cos_d1 
FROM
 version_melody_library t_a, version_melody_search t_b
WHERE t_b.id = 1
ORDER BY
 o_d;
 
-- 使用HNSW索引查询向量 
SELECT id
	,
	features <?> '0.0103757,-0.0512233,0.115912,0.0306533,-0.0369565,-0.0822849,0.0147026,-0.0883773,0.0293781,0.0754001,-0.101713,0.0446254,0.000313702,0.0395059,0.0103388,-0.10857,0.0831431,0.0369271,-0.00735297,0.00318386,0.0960461,0.0253494,0.0707799,0.0406519,-0.146502,0.0153349,0.0717132,0.0329354,-0.0723564,-0.0957046,-0.011365,-0.0198321,-0.0633301,-0.0138867,0.0209664,0.0734466,0.0390921,0.0956636,0.0168159,-0.0318196,-0.0294865,0.0761009,-0.011655,0.152298,-0.0801645,-0.0340168,-0.091394,-0.0571247,0.0775747,-0.0068519,-0.0682228,0.0740612,0.00355041,-0.0389579,-0.0367243,0.02977,-0.0178754,0.0945406,0.0019069,0.0190726,-0.00582011,0.0242098,-0.0490664,0.0699189,-0.0766405,-0.0999905,0.0512349,0.0161044,0.0463886,-0.145589,0.0643599,-0.104054,0.0981023,0.06245,0.143517,-0.0981158,0.034985,-0.0748454,-0.0211035,0.0393017,-0.0423426,-0.00931541,-0.0189619,0.000789204,-0.0900687,-0.091458,0.0030956,0.00305864,0.0781817,0.0259674,0.0239006,-0.0466855,0.0657457,0.0501395,0.00201403,-0.129904,-0.0658315,0.0185226,0.0364833,0.095153,-0.0479706,0.0460201,0.0232972,-0.0438817,0.0423076,-0.0278324,-0.0299966,0.0385006,0.00689985,0.0207789,0.0241247,0.079242,0.0385725,0.0283779,0.0613658,-0.00214942,-0.024576,-0.0214979,-0.0227095,0.0739876,0.0149939,-0.0488034,0.0566438,-0.0334018,-0.0649098,0.177436,-0.000699265,-0.0610644,0.0284796,0.0872894,-0.0362951,0.0648366,0.00684198,-0.0432482,0.0387935,-0.0963403,0.00724373,-0.0768522,-0.0459068,0.0195738,-0.0527789,0.0376479,-0.0408045,-0.00937964,-0.135894,0.038374,-0.0173144,0.139584,0.0761452,0.0257582,0.0649249,-0.0308853,-0.0249384,-0.0462363,0.00350172,0.0261418,-0.0149962,0.0338357,0.00310572,0.0392845,-0.0473759,0.0438511,0.0128222,-0.0445124,0.111465,-0.0209003,0.0273568,-0.00401315,-0.010056,-0.0261298,0.0398294,-0.0181903,-0.0511264,-0.0638069,0.0709738,0.0169831,0.0289444,-0.00751301,0.0333929,-0.0249599,0.0818468,-0.00296295,0.0847351,-0.0900391,0.00703515,0.0267269,-0.00180821,-0.067817,0.0527085,0.0268866,0.0373703,0.0135831,0.0425918,-0.0886959,-0.0106179,-0.082576,-0.00387494,0.0508887,-0.015309,-0.0437559,0.0437996,-0.021275,0.00948386,0.000300521,-0.0104435,-0.0698956,0.0746544,-0.052378,0.00739856,-0.0574827,0.050548,-0.00925615,0.0726212,-0.00924649,-0.0120709,0.0371136,0.11067,-0.0318834,-0.0140657,0.0139252,0.0336886,-0.0336209,-0.00096131,0.0241966,0.039774,-0.00934892,-0.0983028,0.0292723,-0.01112,0.0948006,0.0617013,0.0350783,0.0492754,-0.0192471,-0.0743584,-0.00401492,-0.176885,0.0612225,-0.0342078,-0.00573535,-0.0739013,0.0227512,0.0441616,-0.130958,-0.0805814,0.124215,0.0282091,-0.0143561,-0.0288216,-0.0371116,0.0555073,0.0254531,-0.0402527,0.00681677,0.0199745,-0.041924,0.0813924,-0.0117467,-0.0773737,-0.00300251,0.0328528,0.0484886,-0.0152804,0.0684911,0.0737356,0.00287919,-0.0364697,-0.0869278,-0.053904,-0.0355676,0.00787375,-0.0535734,-0.00102408,-0.160582,-0.0360345,-0.0023895,-0.0411549,-0.0681271,0.0104721,-0.0143556,-0.0616842,0.0546026,0.00936328,0.0469064,0.00243754,0.0291493,0.0691931,-0.0716546,-0.0070803,-0.0734073,0.132919,-0.00898666,0.0241523,0.00518328,0.125173,0.0274769,0.0319476,0.0200359,0.0197402,0.0267486:100:0'::pase AS distance_v ,
	features <?> '0.0103757,-0.0512233,0.115912,0.0306533,-0.0369565,-0.0822849,0.0147026,-0.0883773,0.0293781,0.0754001,-0.101713,0.0446254,0.000313702,0.0395059,0.0103388,-0.10857,0.0831431,0.0369271,-0.00735297,0.00318386,0.0960461,0.0253494,0.0707799,0.0406519,-0.146502,0.0153349,0.0717132,0.0329354,-0.0723564,-0.0957046,-0.011365,-0.0198321,-0.0633301,-0.0138867,0.0209664,0.0734466,0.0390921,0.0956636,0.0168159,-0.0318196,-0.0294865,0.0761009,-0.011655,0.152298,-0.0801645,-0.0340168,-0.091394,-0.0571247,0.0775747,-0.0068519,-0.0682228,0.0740612,0.00355041,-0.0389579,-0.0367243,0.02977,-0.0178754,0.0945406,0.0019069,0.0190726,-0.00582011,0.0242098,-0.0490664,0.0699189,-0.0766405,-0.0999905,0.0512349,0.0161044,0.0463886,-0.145589,0.0643599,-0.104054,0.0981023,0.06245,0.143517,-0.0981158,0.034985,-0.0748454,-0.0211035,0.0393017,-0.0423426,-0.00931541,-0.0189619,0.000789204,-0.0900687,-0.091458,0.0030956,0.00305864,0.0781817,0.0259674,0.0239006,-0.0466855,0.0657457,0.0501395,0.00201403,-0.129904,-0.0658315,0.0185226,0.0364833,0.095153,-0.0479706,0.0460201,0.0232972,-0.0438817,0.0423076,-0.0278324,-0.0299966,0.0385006,0.00689985,0.0207789,0.0241247,0.079242,0.0385725,0.0283779,0.0613658,-0.00214942,-0.024576,-0.0214979,-0.0227095,0.0739876,0.0149939,-0.0488034,0.0566438,-0.0334018,-0.0649098,0.177436,-0.000699265,-0.0610644,0.0284796,0.0872894,-0.0362951,0.0648366,0.00684198,-0.0432482,0.0387935,-0.0963403,0.00724373,-0.0768522,-0.0459068,0.0195738,-0.0527789,0.0376479,-0.0408045,-0.00937964,-0.135894,0.038374,-0.0173144,0.139584,0.0761452,0.0257582,0.0649249,-0.0308853,-0.0249384,-0.0462363,0.00350172,0.0261418,-0.0149962,0.0338357,0.00310572,0.0392845,-0.0473759,0.0438511,0.0128222,-0.0445124,0.111465,-0.0209003,0.0273568,-0.00401315,-0.010056,-0.0261298,0.0398294,-0.0181903,-0.0511264,-0.0638069,0.0709738,0.0169831,0.0289444,-0.00751301,0.0333929,-0.0249599,0.0818468,-0.00296295,0.0847351,-0.0900391,0.00703515,0.0267269,-0.00180821,-0.067817,0.0527085,0.0268866,0.0373703,0.0135831,0.0425918,-0.0886959,-0.0106179,-0.082576,-0.00387494,0.0508887,-0.015309,-0.0437559,0.0437996,-0.021275,0.00948386,0.000300521,-0.0104435,-0.0698956,0.0746544,-0.052378,0.00739856,-0.0574827,0.050548,-0.00925615,0.0726212,-0.00924649,-0.0120709,0.0371136,0.11067,-0.0318834,-0.0140657,0.0139252,0.0336886,-0.0336209,-0.00096131,0.0241966,0.039774,-0.00934892,-0.0983028,0.0292723,-0.01112,0.0948006,0.0617013,0.0350783,0.0492754,-0.0192471,-0.0743584,-0.00401492,-0.176885,0.0612225,-0.0342078,-0.00573535,-0.0739013,0.0227512,0.0441616,-0.130958,-0.0805814,0.124215,0.0282091,-0.0143561,-0.0288216,-0.0371116,0.0555073,0.0254531,-0.0402527,0.00681677,0.0199745,-0.041924,0.0813924,-0.0117467,-0.0773737,-0.00300251,0.0328528,0.0484886,-0.0152804,0.0684911,0.0737356,0.00287919,-0.0364697,-0.0869278,-0.053904,-0.0355676,0.00787375,-0.0535734,-0.00102408,-0.160582,-0.0360345,-0.0023895,-0.0411549,-0.0681271,0.0104721,-0.0143556,-0.0616842,0.0546026,0.00936328,0.0469064,0.00243754,0.0291493,0.0691931,-0.0716546,-0.0070803,-0.0734073,0.132919,-0.00898666,0.0241523,0.00518328,0.125173,0.0274769,0.0319476,0.0200359,0.0197402,0.0267486:100:1'::pase AS similar_v,
	(features <?> '0.0103757,-0.0512233,0.115912,0.0306533,-0.0369565,-0.0822849,0.0147026,-0.0883773,0.0293781,0.0754001,-0.101713,0.0446254,0.000313702,0.0395059,0.0103388,-0.10857,0.0831431,0.0369271,-0.00735297,0.00318386,0.0960461,0.0253494,0.0707799,0.0406519,-0.146502,0.0153349,0.0717132,0.0329354,-0.0723564,-0.0957046,-0.011365,-0.0198321,-0.0633301,-0.0138867,0.0209664,0.0734466,0.0390921,0.0956636,0.0168159,-0.0318196,-0.0294865,0.0761009,-0.011655,0.152298,-0.0801645,-0.0340168,-0.091394,-0.0571247,0.0775747,-0.0068519,-0.0682228,0.0740612,0.00355041,-0.0389579,-0.0367243,0.02977,-0.0178754,0.0945406,0.0019069,0.0190726,-0.00582011,0.0242098,-0.0490664,0.0699189,-0.0766405,-0.0999905,0.0512349,0.0161044,0.0463886,-0.145589,0.0643599,-0.104054,0.0981023,0.06245,0.143517,-0.0981158,0.034985,-0.0748454,-0.0211035,0.0393017,-0.0423426,-0.00931541,-0.0189619,0.000789204,-0.0900687,-0.091458,0.0030956,0.00305864,0.0781817,0.0259674,0.0239006,-0.0466855,0.0657457,0.0501395,0.00201403,-0.129904,-0.0658315,0.0185226,0.0364833,0.095153,-0.0479706,0.0460201,0.0232972,-0.0438817,0.0423076,-0.0278324,-0.0299966,0.0385006,0.00689985,0.0207789,0.0241247,0.079242,0.0385725,0.0283779,0.0613658,-0.00214942,-0.024576,-0.0214979,-0.0227095,0.0739876,0.0149939,-0.0488034,0.0566438,-0.0334018,-0.0649098,0.177436,-0.000699265,-0.0610644,0.0284796,0.0872894,-0.0362951,0.0648366,0.00684198,-0.0432482,0.0387935,-0.0963403,0.00724373,-0.0768522,-0.0459068,0.0195738,-0.0527789,0.0376479,-0.0408045,-0.00937964,-0.135894,0.038374,-0.0173144,0.139584,0.0761452,0.0257582,0.0649249,-0.0308853,-0.0249384,-0.0462363,0.00350172,0.0261418,-0.0149962,0.0338357,0.00310572,0.0392845,-0.0473759,0.0438511,0.0128222,-0.0445124,0.111465,-0.0209003,0.0273568,-0.00401315,-0.010056,-0.0261298,0.0398294,-0.0181903,-0.0511264,-0.0638069,0.0709738,0.0169831,0.0289444,-0.00751301,0.0333929,-0.0249599,0.0818468,-0.00296295,0.0847351,-0.0900391,0.00703515,0.0267269,-0.00180821,-0.067817,0.0527085,0.0268866,0.0373703,0.0135831,0.0425918,-0.0886959,-0.0106179,-0.082576,-0.00387494,0.0508887,-0.015309,-0.0437559,0.0437996,-0.021275,0.00948386,0.000300521,-0.0104435,-0.0698956,0.0746544,-0.052378,0.00739856,-0.0574827,0.050548,-0.00925615,0.0726212,-0.00924649,-0.0120709,0.0371136,0.11067,-0.0318834,-0.0140657,0.0139252,0.0336886,-0.0336209,-0.00096131,0.0241966,0.039774,-0.00934892,-0.0983028,0.0292723,-0.01112,0.0948006,0.0617013,0.0350783,0.0492754,-0.0192471,-0.0743584,-0.00401492,-0.176885,0.0612225,-0.0342078,-0.00573535,-0.0739013,0.0227512,0.0441616,-0.130958,-0.0805814,0.124215,0.0282091,-0.0143561,-0.0288216,-0.0371116,0.0555073,0.0254531,-0.0402527,0.00681677,0.0199745,-0.041924,0.0813924,-0.0117467,-0.0773737,-0.00300251,0.0328528,0.0484886,-0.0152804,0.0684911,0.0737356,0.00287919,-0.0364697,-0.0869278,-0.053904,-0.0355676,0.00787375,-0.0535734,-0.00102408,-0.160582,-0.0360345,-0.0023895,-0.0411549,-0.0681271,0.0104721,-0.0143556,-0.0616842,0.0546026,0.00936328,0.0469064,0.00243754,0.0291493,0.0691931,-0.0716546,-0.0070803,-0.0734073,0.132919,-0.00898666,0.0241523,0.00518328,0.125173,0.0274769,0.0319476,0.0200359,0.0197402,0.0267486:100:1'::pase)*0.5 + 0.5	AS similar_norm1
FROM
	version_melody_library 
ORDER BY
	distance_v ASC 
	LIMIT 10;