计算 SVD
该 SVD 示例使用名为 small_svd 的小数据集。该示例向您展示了如何使用给定的数据集计算 SVD。该表是一个数字矩阵。奇异值分解是使用 SVD 函数计算的。该示例计算表矩阵的 SVD 并将其赋给一个新对象，该对象包含一个向量以及两个矩阵 U 和 V。向量包含奇异值。第一个矩阵 U 包含左奇异向量，V 包含右奇异向量。
开始示例之前，请加载机器学习示例数据。-
创建名为
svdmodel的 SVD 模型。=> SELECT SVD ('svdmodel', 'small_svd', 'x1,x2,x3,x4'); SVD --------------------------------------------------------------Finished in 1 iterations. Accepted Rows: 8 Rejected Rows: 0 (1 row) -
查看
svdmodel的摘要输出。=> SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='svdmodel'); GET_MODEL_SUMMARY ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------- ======= columns ======= index|name -----+---- 1 | x1 2 | x2 3 | x3 4 | x4 =============== singular_values =============== index| value |explained_variance|accumulated_explained_variance -----+--------+------------------+------------------------------ 1 |22.58748| 0.95542 | 0.95542 2 | 3.79176| 0.02692 | 0.98234 3 | 2.55864| 0.01226 | 0.99460 4 | 1.69756| 0.00540 | 1.00000 ====================== right_singular_vectors ====================== index|vector1 |vector2 |vector3 |vector4 -----+--------+--------+--------+-------- 1 | 0.58736| 0.08033| 0.74288|-0.31094 2 | 0.26661| 0.78275|-0.06148| 0.55896 3 | 0.71779|-0.13672|-0.64563|-0.22193 4 | 0.26211|-0.60179| 0.16587| 0.73596 ======== counters ======== counter_name |counter_value ------------------+------------- accepted_row_count| 8 rejected_row_count| 0 iteration_count | 1 =========== call_string =========== SELECT SVD('public.svdmodel', 'small_svd', 'x1,x2,x3,x4'); (1 row) -
创建名为
Umat的新表以获取 U 的值。=> CREATE TABLE Umat AS SELECT APPLY_SVD(id, x1, x2, x3, x4 USING PARAMETERS model_name='svdmodel', exclude_columns='id', key_columns='id') OVER() FROM small_svd; CREATE TABLE -
在
Umat表中查看结果。该表将矩阵转换为新坐标系。=> SELECT * FROM Umat ORDER BY id; id | col1 | col2 | col3 | col4 -----+--------------------+--------------------+---------------------+-------------------- 1 | -0.494871802886819 | -0.161721379259287 | 0.0712816417153664 | -0.473145877877408 2 | -0.17652411036246 | 0.0753183783382909 | -0.678196192333598 | 0.0567124770173372 3 | -0.150974762654569 | -0.589561842046029 | 0.00392654610109522 | 0.360011163271921 4 | -0.44849499240202 | 0.347260956311326 | 0.186958376368345 | 0.378561270493651 5 | -0.494871802886819 | -0.161721379259287 | 0.0712816417153664 | -0.473145877877408 6 | -0.17652411036246 | 0.0753183783382909 | -0.678196192333598 | 0.0567124770173372 7 | -0.150974762654569 | -0.589561842046029 | 0.00392654610109522 | 0.360011163271921 8 | -0.44849499240202 | 0.347260956311326 | 0.186958376368345 | 0.378561270493651 (8 rows) -
然后，我们可以选择将数据从 Umat 转换回 Xmat。首先，我们必须创建 Xmat 表，然后将 APPLY_INVERSE_SVD 函数应用于该表：
=> CREATE TABLE Xmat AS SELECT APPLY_INVERSE_SVD(* USING PARAMETERS model_name='svdmodel', exclude_columns='id', key_columns='id') OVER() FROM Umat; CREATE TABLE -
然后，查看所创建的 Xmat 表中的数据：
=> SELECT id, x1::NUMERIC(5,1), x2::NUMERIC(5,1), x3::NUMERIC(5,1), x4::NUMERIC(5,1) FROM Xmat ORDER BY id; id | x1 | x2 | x3 | x4 ---+-----+-----+-----+----- 1 | 7.0 | 3.0 | 8.0 | 2.0 2 | 1.0 | 1.0 | 4.0 | 1.0 3 | 2.0 | 3.0 | 2.0 | 0.0 4 | 6.0 | 2.0 | 7.0 | 4.0 5 | 7.0 | 3.0 | 8.0 | 2.0 6 | 1.0 | 1.0 | 4.0 | 1.0 7 | 2.0 | 3.0 | 2.0 | 0.0 8 | 6.0 | 2.0 | 7.0 | 4.0 (8 rows)