{"id":2167,"date":"2025-09-19T17:39:55","date_gmt":"2025-09-19T09:39:55","guid":{"rendered":"http:\/\/www.821121.com\/?p=2167"},"modified":"2026-04-23T23:48:34","modified_gmt":"2026-04-23T15:48:34","slug":"23ai-%e6%96%b0%e7%89%b9%e6%80%a7%e4%b9%8bsql-%e5%b1%9e%e6%80%a7%e5%9b%be","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=2167","title":{"rendered":"23ai \u65b0\u7279\u6027\u4e4bSQL \u5c5e\u6027\u56fe"},"content":{"rendered":"<p>Oracle 23ai\u7684SQL \u5c5e\u6027\u56fe\uff0c\u6838\u5fc3\u662f\u5c06\u56fe\u6570\u636e\u6a21\u578b\u4e0eSQL\u8bed\u8a00\u6df1\u5ea6\u878d\u5408\uff0c\u5b9e\u73b0\u201c\u5173\u7cfb\u6570\u636e\u2192\u5c5e\u6027\u56fe\u2192SQL\u67e5\u8be2\u201d\u7684\u5168\u6d41\u7a0b\u539f\u751f\u652f\u6301\u3002\u5c5e\u6027\u56fe\u7531\u9876\u70b9\uff08Vertices\uff09\u3001\u8fb9\uff08Edges\uff09\u7ec4\u6210\uff0c\u9876\u70b9\u4ee3\u8868\u6570\u636e\u5b9e\u4f53\uff08\u5982\u7528\u6237\u3001\u8ba2\u5355\u3001\u4ea7\u54c1\uff09\uff0c\u8fb9\u4ee3\u8868\u5b9e\u4f53\u95f4\u7684\u5173\u8054\u5173\u7cfb\uff08\u5982\u201c\u7528\u6237\u8d2d\u4e70\u4ea7\u54c1\u201d\u201c\u670b\u53cb\u5173\u7cfb\u201d\u201c\u4f9b\u5e94\u94fe\u4f9d\u8d56\u201d\uff09\uff0c\u9876\u70b9\u548c\u8fb9\u90fd\u53ef\u4ee5\u643a\u5e26\u952e\u503c\u5bf9\u5f62\u5f0f\u7684\u5c5e\u6027\uff08\u5982\u7528\u6237\u7684\u59d3\u540d\u3001\u5e74\u9f84\uff0c\u8ba2\u5355\u7684\u91d1\u989d\u3001\u65e5\u671f\uff09\u3002<\/p>\n<p>\u4e0e\u4e13\u7528\u56fe\u6570\u636e\u5e93\u4e0d\u540c\uff0cSQL \u5c5e\u6027\u56fe\u65e0\u9700\u5c06\u6570\u636e\u5bfc\u51fa\u5230\u5916\u90e8\u7cfb\u7edf\uff0c\u800c\u662f\u76f4\u63a5\u57fa\u4e8e\u4f01\u4e1a\u73b0\u6709\u5173\u7cfb\u8868\u521b\u5efa\uff0c\u6570\u636e\u4ecd\u5b58\u50a8\u5728\u5173\u7cfb\u8868\u4e2d\uff0c\u5c5e\u6027\u56fe\u672c\u8d28\u4e0a\u662f\u5bf9\u5173\u7cfb\u6570\u636e\u7684\u201c\u89c6\u56fe\u7ea7\u5c01\u88c5\u201d\u3002\u8fd9\u4e00\u8bbe\u8ba1\u4e0d\u4ec5\u907f\u514d\u4e86\u6570\u636e\u5197\u4f59\u4e0e\u540c\u6b65\u95ee\u9898\uff0c\u8fd8\u8ba9\u5f00\u53d1\u8005\u80fd\u591f\u7528\u719f\u6089\u7684SQL\u8bed\u6cd5\u7f16\u5199\u56fe\u67e5\u8be2\uff0c\u65e0\u9700\u5b66\u4e60\u65b0\u7684\u56fe\u67e5\u8be2\u8bed\u8a00\uff08\u5982PGQL\uff09\uff0c\u5927\u5e45\u964d\u4f4e\u4e86\u56fe\u5206\u6790\u7684\u95e8\u69db\uff0c\u5b9e\u73b0\u4e86\u5173\u7cfb\u6570\u636e\u4e0e\u56fe\u6570\u636e\u7684\u7edf\u4e00\u7ba1\u7406\u4e0e\u67e5\u8be2\u3002<\/p>\n<p>\u540c\u65f6\uff0cSQL \u5c5e\u6027\u56fe\u9075\u5faaSQL:2023\u6807\u51c6\uff0c\u652f\u6301GRAPH_TABLE\u8fd0\u7b97\u7b26\u4e0eMATCH\u5b50\u53e5\uff0c\u8ba9\u56fe\u67e5\u8be2\u80fd\u591f\u4e0e\u4f20\u7edfSQL\u67e5\u8be2\u65e0\u7f1d\u7ed3\u5408\uff0c\u9002\u914d\u4f01\u4e1a\u73b0\u6709\u7684\u6280\u672f\u6808\u4e0e\u5f00\u53d1\u4e60\u60ef\uff0c\u771f\u6b63\u5b9e\u73b0\u4e86\u201c\u7528SQL\u641e\u5b9a\u56fe\u5206\u6790\u201d\u3002<\/p>\n<p>\u63a5\u4e0b\u6765\u6a21\u62df\u4e24\u4e2a\u4e1a\u52a1\u573a\u666f<\/p>\n<h4>\u573a\u666f1:\u793e\u4ea4\u7f51\u7edc\u597d\u53cb\u5173\u7cfb\u56fe\u67e5\u8be2<\/h4>\n<p>\u9700\u6c42\uff1a\u57fa\u4e8e\u7528\u6237\u8868\u4e0e\u597d\u53cb\u5173\u7cfb\u8868\uff0c\u521b\u5efa\u5c5e\u6027\u56fe\uff0c\u67e5\u8be2\u7528\u6237\u4e4b\u95f4\u7684\u597d\u53cb\u5173\u7cfb\u3001\u6700\u77ed\u597d\u53cb\u94fe\u3002<\/p>\n<ul>\n<li>\u6b65\u9aa41\uff1a\u521b\u5efa\u5e95\u5c42\u5173\u7cfb\u8868\u5e76\u63d2\u5165\u6d4b\u8bd5\u6570\u636e\n<pre><code class=\"language-sql\">-- \u521b\u5efa\u7528\u6237\u8868\uff08\u9876\u70b9\u8868\uff09\nCREATE TABLE users (\nuser_id NUMBER PRIMARY KEY,\nuser_name VARCHAR2(100) NOT NULL,\nage NUMBER,\ncity VARCHAR2(50)\n);\n-- \u521b\u5efa\u597d\u53cb\u5173\u7cfb\u8868\uff08\u8fb9\u8868\uff09\nCREATE TABLE friend_relation (\nrelation_id NUMBER PRIMARY KEY,\nfrom_user_id NUMBER REFERENCES users(user_id),\nto_user_id NUMBER REFERENCES users(user_id),\nrelation_date DATE DEFAULT SYSDATE,\n-- \u8fb9\u7684\u5c5e\u6027\uff1a\u597d\u53cb\u5173\u7cfb\u7c7b\u578b\uff08\u540c\u5b66\u3001\u540c\u4e8b\u3001\u5bb6\u4eba\uff09\nrelation_type VARCHAR2(20)\n);\n-- \u63d2\u5165\u6d4b\u8bd5\u6570\u636e\nINSERT INTO users VALUES (1, &#039;\u5f20\u4e09&#039;, 25, &#039;\u5317\u4eac&#039;);\nINSERT INTO users VALUES (2, &#039;\u674e\u56db&#039;, 26, &#039;\u4e0a\u6d77&#039;);\nINSERT INTO users VALUES (3, &#039;\u738b\u4e94&#039;, 24, &#039;\u5317\u4eac&#039;);\nINSERT INTO users VALUES (4, &#039;\u8d75\u516d&#039;, 27, &#039;\u5e7f\u5dde&#039;);\nINSERT INTO friend_relation VALUES (101, 1, 2, SYSDATE, &#039;\u540c\u5b66&#039;);\nINSERT INTO friend_relation VALUES (102, 1, 3, SYSDATE, &#039;\u540c\u4e8b&#039;);\nINSERT INTO friend_relation VALUES (103, 2, 4, SYSDATE, &#039;\u670b\u53cb&#039;);\nINSERT INTO friend_relation VALUES (104, 3, 4, SYSDATE, &#039;\u540c\u5b66&#039;);\nCOMMIT;<\/code><\/pre>\n<\/li>\n<li>\u6b65\u9aa42\uff1a\u521b\u5efaSQL \u5c5e\u6027\u56fe\n<pre><code class=\"language-sql\">-- \u521b\u5efa\u5c5e\u6027\u56fe\uff0c\u5b9a\u4e49\u9876\u70b9\u4e0e\u8fb9\nCREATE PROPERTY GRAPH social_graph\n-- \u5b9a\u4e49\u9876\u70b9\uff1a\u7528\u6237\nVERTEX TABLES (\nusers \n  KEY (user_id) \n  PROPERTIES (user_id, user_name, age, city)\n)\n-- \u5b9a\u4e49\u8fb9\uff1a\u597d\u53cb\u5173\u7cfb\nEDGE TABLES (\nfriend_relation\n  KEY (relation_id)\n  SOURCE KEY (from_user_id) REFERENCES users(user_id)\n  DESTINATION KEY (to_user_id) REFERENCES users(user_id)\n  PROPERTIES (relation_id, relation_type, relation_date)\n);<\/code><\/pre>\n<\/li>\n<li>\u6b65\u9aa43\uff1a\u6267\u884c\u56fe\u67e5\u8be2\n<pre><code class=\"language-sql\">-- 1. \u67e5\u8be2\u5f20\u4e09\uff08user_id=1\uff09\u7684\u6240\u6709\u597d\u53cb\nSELECT t.user_name AS \u597d\u53cb\u59d3\u540d, t.age AS \u597d\u53cb\u5e74\u9f84, t.relation_type AS \u5173\u7cfb\u7c7b\u578b\nFROM GRAPH_TABLE (\nsocial_graph\nMATCH (u:users)-[r:friend_relation]-&gt;(v:users)\nWHERE u.user_id = 1\nCOLUMNS (v.user_name, v.age, r.relation_type)\n) t;\n-- 2. \u67e5\u8be2\u5f20\u4e09\uff08user_id=1\uff09\u5230\u8d75\u516d\uff08user_id=4\uff09\u7684\u6700\u77ed\u597d\u53cb\u94fe\nSELECT t.path_length, t.user_names\nFROM GRAPH_TABLE (\nsocial_graph\nMATCH SHORTEST PATH (u:users)-[*]-&gt;(v:users)\nWHERE u.user_id = 1 AND v.user_id = 4\nCOLUMNS (\nCOUNT(r.relation_id) AS path_length,\nLISTAGG(u.user_name || &#039;-&gt;&#039; || v.user_name, &#039;,&#039;) WITHIN GROUP (ORDER BY r.relation_id) AS user_names\n)\n) t;<\/code><\/pre>\n<h4>\u573a\u666f2\uff1a\u7535\u5546\u4ea7\u54c1\u5173\u8054\u63a8\u8350\u67e5\u8be2<\/h4>\n<p>\u9700\u6c42\uff1a\u57fa\u4e8e\u7528\u6237\u3001\u8ba2\u5355\u3001\u4ea7\u54c1\u8868\uff0c\u521b\u5efa\u5c5e\u6027\u56fe\uff0c\u67e5\u8be2\u201c\u8d2d\u4e70\u4e86\u67d0\u4ea7\u54c1\u7684\u7528\u6237\u8fd8\u8d2d\u4e70\u4e86\u54ea\u4e9b\u4ea7\u54c1\u201d\uff0c\u7528\u4e8e\u4ea7\u54c1\u63a8\u8350\u3002<\/p>\n<\/li>\n<li>\u6b65\u9aa41\uff1a\u590d\u7528\u7535\u5546\u8ba2\u5355\u5e95\u5c42\u8868\uff08\u524d\u6587\u5df2\u521b\u5efa\uff09\uff0c\u521b\u5efa\u5c5e\u6027\u56fe\n<pre><code class=\"language-sql\">-- \u521b\u5efa\u7535\u5546\u4ea7\u54c1\u5173\u8054\u5c5e\u6027\u56fe\nCREATE PROPERTY GRAPH product_recommend_graph\n-- \u5b9a\u4e49\u9876\u70b9\uff1a\u7528\u6237\u3001\u4ea7\u54c1\nVERTEX TABLES (\ncustomer \n  KEY (cust_id) \n  PROPERTIES (cust_id, cust_name),\norder_item\n  KEY (item_id)\n  PROPERTIES (item_id, product_name, unit_price)\n)\n-- \u5b9a\u4e49\u8fb9\uff1a\u7528\u6237\u8d2d\u4e70\u4ea7\u54c1\uff08\u901a\u8fc7\u8ba2\u5355\u5173\u8054\uff09\nEDGE TABLES (\norders\n  KEY (order_id)\n  SOURCE KEY (cust_id) REFERENCES customer(cust_id)\n  DESTINATION KEY (order_id) REFERENCES order_item(order_id)\n  PROPERTIES (order_id, total_amount, order_date)\n);<\/code><\/pre>\n<p>\u6b65\u9aa42\uff1a\u67e5\u8be2\u4ea7\u54c1\u5173\u8054\u63a8\u8350\u6570\u636e<\/p>\n<pre><code class=\"language-sql\">-- \u67e5\u8be2\u8d2d\u4e70\u4e86\u201c\u624b\u673a\u201d\u7684\u7528\u6237\u8fd8\u8d2d\u4e70\u4e86\u54ea\u4e9b\u4ea7\u54c1\nSELECT t.recommend_product, COUNT(t.recommend_product) AS \u8d2d\u4e70\u6b21\u6570\nFROM GRAPH_TABLE (\nproduct_recommend_graph\nMATCH (c:customer)-[o:orders]-&gt;(i1:order_item),\n    (c)-[o2:orders]-&gt;(i2:order_item)\nWHERE i1.product_name = &#039;\u624b\u673a&#039; AND i2.product_name != &#039;\u624b\u673a&#039;\nCOLUMNS (i2.product_name AS recommend_product)\n) t\nGROUP BY t.recommend_product\nORDER BY \u8d2d\u4e70\u6b21\u6570 DESC;<\/code><\/pre>\n<p>\u6267\u884c\u540e\uff0c\u4f1a\u8fd4\u56de\u8d2d\u4e70\u8fc7\u624b\u673a\u7684\u7528\u6237\u540c\u65f6\u8d2d\u4e70\u7684\u5176\u4ed6\u4ea7\u54c1\u53ca\u8d2d\u4e70\u6b21\u6570\uff0c\u53ef\u76f4\u63a5\u7528\u4e8e\u4ea7\u54c1\u63a8\u8350\u573a\u666f\u3002<\/p>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Oracle 23ai\u7684SQL \u5c5e\u6027\u56fe\uff0c\u6838\u5fc3\u662f\u5c06\u56fe<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57,58],"tags":[],"class_list":["post-2167","post","type-post","status-publish","format-standard","hentry","category-oracle-23ai","category--oracle-23c"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2167","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2167"}],"version-history":[{"count":3,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2167\/revisions"}],"predecessor-version":[{"id":2170,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/2167\/revisions\/2170"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2167"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}