sql - SQLite)在图结构上查询以获取和获取链接

Question

背景信息我将用户的Wikipedia浏览历史记录存储在Android应用程序中的SQLite db中，以维护并以图形形式显示其浏览历史记录。


这是图形的结构-每次用户单击Wiki页面中的链接（到另一个Wiki页面）时，都会为新页面创建WikiPage（如果尚不存在）和PageVisit。还添加了通过其VisitEdge返回到先前WikiPage的PageVisit（清除为泥浆吗？）

将所有这些写入我的数据库都可以正常工作。

我正在尝试编写两个SQL语句INLINKS和OUTLINKS，因此，如果用户使用的是这样的WikiPage，则查询将分别返回该页面的out链接和inlinks。返回的行将按时间顺序（最新的排在前），不显示重复，并在每个条目旁边显示对历史访问该页面的次数的计数：

例如用户在B上，因此查询将返回

INLINKS :    (row1) A._id, 2._id, 2.wikipage_id, 2.created_at, COUNT = 1

OUTLINKS :   (row1) C._id, 6._id, 6.wikipage_id, 6.created_at, COUNT = 2
             (row2) D._id, 5._id, 5.wikipage_id, 5.created_at, COUNT = 1

例如如果用户在C

INLINKS :    (row1) B._id, 3._id, 3.wikipage_id, 3.created_at, COUNT = 1
             (row2) A._id, 1._id, 1.wikipage_id, 1.created_at, COUNT = 1

OUTLINKS:    (no rows)   

注意：我现在只返回所有列...先事！

这是当前的OUTLINK语句：

private static final String SELECT_OUTLINKS_QUERY = "SELECT DISTINCT wp.*,ve.*,pv.* FROM " + TABLE_NAME_PAGEVISITS + " pv "     
      + " JOIN " + TABLE_NAME_VISITEDGES + " ve ON pva." + PageVisit.KEY_ID + " = ve." + VisitEdge.KEY_FROM_PAGEVISIT_ID
      + " JOIN " + TABLE_NAME_PAGEVISITS + " pva ON pva." + PageVisit.KEY_WIKIPAGE_ID + " = ve." + VisitEdge.KEY_TO_PAGEVISIT_ID
      + " JOIN " + TABLE_NAME_WIKIPAGES + " wp ON wp." + WikiPage.KEY_ID + " = pv." + PageVisit.KEY_WIKIPAGE_ID
      + " WHERE pv." + PageVisit.KEY_WIKIPAGE_ID + " =?" // ? is the current wikiPage id!
      + " ORDER BY pva." + PageVisit.KEY_CREATED_AT + " DESC";

不用说，当我谈到SQL时我还是很环保的，而且运行的不是很好-它偶尔会返回一些东西，但很少返回正确的东西，大多数时候没有返回。我也尝试过放置 COUNT语句，但是运气还差得多。

INLINKS语句类似，除了将“ to”替换为“ from”。

有谁能帮助我-我对此有些混乱。例如，定义两个PageVisit表 pv和 pva是否正确？我如何在那儿数钱？



一个答案...不是我使用的那个
感谢@CL。对于下面他的回答-我实际上最终使用了他的解决方案，但是在询问了qn之后，我试图解决我的寂寞问题。这就是我最终的结果。我将其包括在内是为了完整性。我会对这两种方法的利弊都感兴趣。

SELECT DISTINCT wp.*,c.n,mtable.mx
    FROM PageVisit pv
    JOIN PageVisit pv1 ON pv1._id = ve.topagevisit_id
    JOIN VisitEdge ve ON pv._id = ve.frompagevisit_id
    JOIN WikiPage wp ON wp._id = pv1.wikipage_id
    JOIN (SELECT WikiPage_id,MAX(created_at) AS mx FROM PageVisit GROUP BY     wikipage_id) mtable ON wp._id = mtable.wikipage_id
    JOIN (SELECT WikiPage_id,COUNT(*) AS n FROM PageVisit GROUP BY wikipage_id) c ON wp._id = c.wikipage_id
    WHERE pv.wikipage_id = ?
    ORDER BY mtable.mx DESC;

Answer 1

首先，有趣的页面访问是：

SELECT *
FROM PageVisit
WHERE wikipage_id = ?

对于OUTLINKS，可以查询相应的出站访问边缘：

SELECT *
FROM VisitEdge
WHERE from_pagevisit_id IN (SELECT _id
                            FROM PageVisit
                            WHERE wikipage_id = ?)

现在，这可用于查找目标页面访问：

SELECT *
FROM PageVisit
WHERE _id IN (SELECT to_pagevisit_id
              FROM VisitEdge
              WHERE from_pagevisit_id IN (SELECT _id
                                          FROM PageVisit
                                          WHERE wikipage_id = ?))

可以通过联接添加相应的Wiki页面数据：

SELECT *
FROM WikiPage
JOIN PageVisit ON WikiPage._id = PageVisit.wikipage_id
WHERE PageVisit._id IN (SELECT to_pagevisit_id
                        FROM VisitEdge
                        WHERE from_pagevisit_id IN (SELECT _id
                                                    FROM PageVisit
                                                    WHERE wikipage_id = ?))

要删除页面重复项，请按页面分组。
一个输出记录可以有多个访问记录，因此不再可以直接输出 PageVisit值；必须使用一些汇总函数来处理它们：

SELECT WikiPage.*,
       MAX(PageVisit.created_at) AS last_date,
       COUNT(*) AS "count"
FROM WikiPage
JOIN PageVisit ON WikiPage._id = PageVisit.wikipage_id
WHERE PageVisit._id IN (SELECT to_pagevisit_id
                        FROM VisitEdge
                        WHERE from_pagevisit_id IN (SELECT _id
                                                    FROM PageVisit
                                                    WHERE wikipage_id = ?))
GROUP BY WikiPage._id
ORDER BY last_date DESC

SQLFiddle