关于Frequent Itemset的PL / SQL问题(PL/SQL issue concerning Frequent Itemset)

关于Frequent Itemset的PL / SQL问题(PL/SQL issue concerning Frequent Itemset)

我正在尝试构建一个PL / SQL应用程序来从一组给定数据中挖掘频繁项目集，我遇到了一些障碍。 我的PL / SQL技能不如我想的那么好，所以也许你们中的一个可以帮助我更好地理解这一点。

首先，我正在使用Oracle数据挖掘过程：* DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL *

在阅读文档时，我遇到了以下示例，我已经操作过来查询我的数据集：

CREATE OR REPLACE TYPE FI_VARCHAR_NT AS TABLE OF NUMBER; / CREATE TYPE fi_res AS OBJECT ( itemset FI_VARCHAR_NT, support NUMBER, length NUMBER, total_tranx NUMBER ); / CREATE TYPE fi_coll AS TABLE OF fi_res; / create or replace PROCEDURE freq_itemset_test is cursor freqC is SELECT itemset FROM table( CAST(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(CURSOR(SELECT sale.customerid, sale.productid FROM Sale INNER JOIN Customer ON customer.customerid = sale.customerid WHERE customer.region = 'Canada' ) ,0,2, 2, NULL, NULL) AS fi_coll)); coll_nt FI_VARCHAR_NT; num_rows int; num_itms int; BEGIN num_rows := 0; num_itms := 0; OPEN freqC; LOOP FETCH freqC INTO coll_nt; EXIT WHEN freqC%NOTFOUND; num_rows := num_rows + 1; num_itms := num_itms + coll_nt.count; END LOOP; DBMS_OUTPUT.PUT_LINE('Rows: ' || num_rows || ' Columns: ' || num_itms); CLOSE freqC; END;

我使用Oracle FI_TRANSACTIONAL而不是直接SQL的原因是我需要对K的多个动态值重复这个分析，那么为什么要重新发明轮子呢？ 最终，我的目标是引用过程返回的每个单独的项集，并根据某些查询逻辑返回具有最高支持的集。 我将这个PL / SQL块合并到另一个块中，基本上根据数据内容将查询中的文字从“加拿大”更改为多个其他区域。

我的问题是：如何实际获得游标（freqC）返回的数据的程序化引用？ 显然，我不需要计算行和列，但这是示例的一部分。 在找到最多的项目集后，我想用DBMS打印行打印出项目集。 当我在调试器中查看它时，我看到每次获取光标实际上返回一个项集（在这种情况下，k = 2，所以两个项）。 但是我如何以编程方式实际触摸它们呢？ 我想抓住这些套装以及fi_res.support。

一如既往，感谢大家分享他们的才华！

I'm trying to build a PL/SQL application to mine frequent item sets out of a set of given data and I've run into a bit of a snag. My PL/SQL skills aren't as good as I'd like them to be, so perhaps one of you can help me understand this a bit better.

So to begin, I'm using the Oracle data mining procedure: *DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL*

While reading the documentation, I came across the following example which I have manipulated to query over my data set:

CREATE OR REPLACE TYPE FI_VARCHAR_NT AS TABLE OF NUMBER; / CREATE TYPE fi_res AS OBJECT ( itemset FI_VARCHAR_NT, support NUMBER, length NUMBER, total_tranx NUMBER ); / CREATE TYPE fi_coll AS TABLE OF fi_res; / create or replace PROCEDURE freq_itemset_test is cursor freqC is SELECT itemset FROM table( CAST(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(CURSOR(SELECT sale.customerid, sale.productid FROM Sale INNER JOIN Customer ON customer.customerid = sale.customerid WHERE customer.region = 'Canada' ) ,0,2, 2, NULL, NULL) AS fi_coll)); coll_nt FI_VARCHAR_NT; num_rows int; num_itms int; BEGIN num_rows := 0; num_itms := 0; OPEN freqC; LOOP FETCH freqC INTO coll_nt; EXIT WHEN freqC%NOTFOUND; num_rows := num_rows + 1; num_itms := num_itms + coll_nt.count; END LOOP; DBMS_OUTPUT.PUT_LINE('Rows: ' || num_rows || ' Columns: ' || num_itms); CLOSE freqC; END;

My reasoning for using the Oracle FI_TRANSACTIONAL over straight SQL is that I will need to repeat this analysis for multiple dynamic values of K, so why reinvent the wheel? Ultimately, my goal is to reference each individual item sets returned by the procedure and return the set with the highest support based on some query logic. I will be incorporating this block of PL/SQL into another that basically changes the literal in the query from 'Canada' to multiple other regions based on the content of the data.

My question is: How can I actually get a programmatic reference on the data returned by the cursor (freqC)? Obviously I do not need to count the rows and columns, but that was part of the example. I'd like to print out the item sets with DBMS print line after I've found the most occurring item set. When I view this in a debugger, I see that each fetch of the cursor actually returns an item set (in this case, k=2, so two items). But how do I actually touch them programmatically? I'd like to grab the sets themselves as well as fi_res.support.

As always, thanks to everyone for sharing their brilliance!

最满意答案

您正在将数据提取到嵌套表中。 因此，要查看其中的数据，您需要遍历嵌套表：

FOR i IN coll_nt.FIRST .. coll_nt.LAST LOOP dbms_output.put_line(i||': '||coll_nt(i)); END LOOP;

有关嵌套表和其他类型集合的更多信息，请参阅演示文稿： http ： //www.toadworld.com/platforms/oracle/w/wiki/8253.everything-you-need-to-know-about-集合，但是，是-怕到ask.aspx

You are fetching your data into a nested table. So to see the data in there, you would need to loop over the nested table:

FOR i IN coll_nt.FIRST .. coll_nt.LAST LOOP dbms_output.put_line(i||': '||coll_nt(i)); END LOOP;

For much more information on nested tables and other types of collections, see the presentation at: http://www.toadworld.com/platforms/oracle/w/wiki/8253.everything-you-need-to-know-about-collections-but-were-afraid-to-ask.aspx