AQL高级查询 #

一、高级查询概述 #

高级查询技术可以帮助你处理更复杂的数据分析场景，提高查询效率和可读性。

1.1 高级查询技术 #

技术	说明
子查询	在查询中嵌套查询
CTE	公用表表达式
窗口函数	分析函数
UNION	合并结果集

二、子查询 #

2.1 基本子查询 #

aql

FOR user IN users
    LET orderCount = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT WITH COUNT INTO count
            RETURN count
    )[0]
    RETURN {
        name: user.name,
        orderCount: orderCount || 0
    }

2.2 子查询返回数组 #

aql

FOR user IN users
    LET userOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            RETURN {
                orderId: order._key,
                amount: order.amount
            }
    )
    RETURN {
        name: user.name,
        orders: userOrders
    }

2.3 子查询关联 #

aql

FOR order IN orders
    LET customer = (
        FOR user IN users
            FILTER user._key == order.userId
            RETURN user
    )[0]
    RETURN {
        orderId: order._key,
        customerName: customer.name,
        amount: order.amount
    }

2.4 子查询过滤 #

aql

FOR user IN users
    LET recentOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            AND order.createdAt > DATE_SUBTRACT(DATE_NOW(), 30, "days")
            RETURN order
    )
    FILTER LENGTH(recentOrders) > 0
    RETURN {
        name: user.name,
        recentOrderCount: LENGTH(recentOrders)
    }

2.5 子查询聚合 #

aql

FOR user IN users
    LET stats = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT AGGREGATE
                totalAmount = SUM(order.amount),
                avgAmount = AVG(order.amount),
                orderCount = COUNT()
            RETURN {
                totalAmount: totalAmount,
                avgAmount: avgAmount,
                orderCount: orderCount
            }
    )[0]
    RETURN {
        name: user.name,
        stats: stats || { totalAmount: 0, avgAmount: 0, orderCount: 0 }
    }

三、CTE（公用表表达式） #

3.1 基本CTE #

使用LET定义CTE：

aql

LET activeUsers = (
    FOR user IN users
        FILTER user.status == "active"
        RETURN user
)
FOR user IN activeUsers
    RETURN user.name

3.2 多个CTE #

aql

LET activeUsers = (
    FOR user IN users
        FILTER user.status == "active"
        RETURN user
)
LET recentOrders = (
    FOR order IN orders
        FILTER order.createdAt > DATE_SUBTRACT(DATE_NOW(), 30, "days")
        RETURN order
)
FOR user IN activeUsers
    LET userOrders = (
        FOR order IN recentOrders
            FILTER order.userId == user._key
            RETURN order
    )
    RETURN {
        user: user.name,
        orderCount: LENGTH(userOrders)
    }

3.3 CTE链式查询 #

aql

LET usersWithOrders = (
    FOR user IN users
        LET orderCount = (
            FOR order IN orders
                FILTER order.userId == user._key
                COLLECT WITH COUNT INTO count
                RETURN count
        )[0]
        RETURN {
            user: user,
            orderCount: orderCount || 0
        }
)
LET topUsers = (
    FOR u IN usersWithOrders
        FILTER u.orderCount > 5
        RETURN u
)
FOR u IN topUsers
    SORT u.orderCount DESC
    RETURN {
        name: u.user.name,
        orderCount: u.orderCount
    }

3.4 CTE与聚合 #

aql

LET cityStats = (
    FOR user IN users
        COLLECT city = user.city AGGREGATE
            userCount = COUNT(),
            avgAge = AVG(user.age)
        RETURN {
            city: city,
            userCount: userCount,
            avgAge: avgAge
        }
)
FOR stat IN cityStats
    FILTER stat.userCount > 100
    SORT stat.userCount DESC
    RETURN stat

四、窗口函数 #

4.1 ROW_NUMBER #

aql

FOR user IN users
    SORT user.score DESC
    LET rank = ROW_NUMBER()
    RETURN {
        name: user.name,
        score: user.score,
        rank: rank
    }

4.2 RANK #

aql

FOR user IN users
    SORT user.score DESC
    LET rank = RANK(user.score)
    RETURN {
        name: user.name,
        score: user.score,
        rank: rank
    }

4.3 DENSE_RANK #

aql

FOR user IN users
    SORT user.score DESC
    LET rank = DENSE_RANK(user.score)
    RETURN {
        name: user.name,
        score: user.score,
        rank: rank
    }

4.4 分组排名 #

aql

FOR user IN users
    SORT user.city, user.score DESC
    LET rank = ROW_NUMBER()
    RETURN {
        city: user.city,
        name: user.name,
        score: user.score,
        cityRank: rank
    }

4.5 累计计算 #

aql

FOR order IN orders
    SORT order.createdAt
    LET runningTotal = SUM(order.amount)
    RETURN {
        date: order.createdAt,
        amount: order.amount,
        runningTotal: runningTotal
    }

4.6 移动平均 #

aql

FOR order IN orders
    SORT order.createdAt
    LET avgAmount = AVG(order.amount)
    RETURN {
        date: order.createdAt,
        amount: order.amount,
        avgAmount: avgAmount
    }

五、UNION操作 #

5.1 UNION #

合并结果集（保留重复）：

aql

LET result1 = (
    FOR user IN users
        FILTER user.city == "北京"
        RETURN user.name
)
LET result2 = (
    FOR user IN users
        FILTER user.age > 30
        RETURN user.name
)
FOR name IN UNION(result1, result2)
    RETURN name

5.2 UNION_DISTINCT #

合并结果集（去重）：

aql

LET result1 = (
    FOR user IN users
        FILTER user.city == "北京"
        RETURN user.name
)
LET result2 = (
    FOR user IN users
        FILTER user.age > 30
        RETURN user.name
)
FOR name IN UNION_DISTINCT(result1, result2)
    RETURN name

5.3 MINUS #

差集：

aql

LET allUsers = (
    FOR user IN users
        RETURN user._key
)
LET activeUsers = (
    FOR user IN users
        FILTER user.status == "active"
        RETURN user._key
)
FOR key IN MINUS(allUsers, activeUsers)
    RETURN key

5.4 INTERSECTION #

交集：

aql

LET beijingUsers = (
    FOR user IN users
        FILTER user.city == "北京"
        RETURN user._key
)
LET activeUsers = (
    FOR user IN users
        FILTER user.status == "active"
        RETURN user._key
)
FOR key IN INTERSECTION(beijingUsers, activeUsers)
    RETURN key

六、复杂条件查询 #

6.1 多表关联查询 #

aql

FOR order IN orders
    FOR user IN users
        FILTER order.userId == user._key
        FOR product IN products
            FILTER order.productId == product._key
            RETURN {
                orderId: order._key,
                userName: user.name,
                productName: product.name,
                amount: order.amount
            }

6.2 左连接效果 #

aql

FOR user IN users
    LET orders = (
        FOR order IN orders
            FILTER order.userId == user._key
            RETURN order
    )
    RETURN {
        user: user.name,
        orders: orders,
        orderCount: LENGTH(orders)
    }

6.3 条件聚合 #

aql

FOR user IN users
    LET orderStats = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT AGGREGATE
                total = SUM(order.amount),
                count = COUNT(),
                avgAmount = AVG(order.amount)
            RETURN { total, count, avgAmount }
    )[0]
    LET lastOrder = (
        FOR order IN orders
            FILTER order.userId == user._key
            SORT order.createdAt DESC
            LIMIT 1
            RETURN order
    )[0]
    RETURN {
        name: user.name,
        stats: orderStats,
        lastOrder: lastOrder
    }

6.4 分层查询 #

aql

LET level1 = (
    FOR category IN categories
        FILTER category.parentId == null
        RETURN category
)
FOR cat1 IN level1
    LET level2 = (
        FOR category IN categories
            FILTER category.parentId == cat1._key
            RETURN category
    )
    RETURN {
        category: cat1.name,
        subCategories: level2
    }

七、日期处理 #

7.1 日期范围查询 #

aql

FOR order IN orders
    FILTER order.createdAt >= DATE_SUBTRACT(DATE_NOW(), 7, "days")
    AND order.createdAt < DATE_NOW()
    RETURN order

7.2 按日期分组 #

aql

FOR order IN orders
    COLLECT date = DATE_FORMAT(order.createdAt, "%Y-%m-%d")
    AGGREGATE
        total = SUM(order.amount),
        count = COUNT()
    SORT date DESC
    RETURN {
        date: date,
        total: total,
        count: count
    }

7.3 按月份分组 #

aql

FOR order IN orders
    COLLECT year = DATE_YEAR(order.createdAt),
             month = DATE_MONTH(order.createdAt)
    AGGREGATE
        total = SUM(order.amount),
        count = COUNT()
    SORT year DESC, month DESC
    RETURN {
        year: year,
        month: month,
        total: total,
        count: count
    }

7.4 日期比较 #

aql

FOR user IN users
    LET daysSinceLogin = DATE_DIFF(user.lastLogin, DATE_NOW(), "days")
    FILTER daysSinceLogin > 30
    RETURN {
        name: user.name,
        daysSinceLogin: daysSinceLogin
    }

八、字符串处理 #

8.1 字符串搜索 #

aql

FOR user IN users
    FILTER CONTAINS(LOWER(user.name), LOWER(@keyword))
    RETURN user

8.2 字符串分割 #

aql

FOR user IN users
    LET nameParts = SPLIT(user.name, " ")
    RETURN {
        firstName: nameParts[0],
        lastName: nameParts[1] || ""
    }

8.3 字符串拼接 #

aql

FOR user IN users
    RETURN {
        fullName: CONCAT(user.firstName, " ", user.lastName),
        displayName: CONCAT(user.name, " (", user.email, ")")
    }

8.4 正则表达式 #

aql

FOR user IN users
    FILTER user.phone =~ "^1[3-9]\\d{9}$"
    RETURN user

九、数组处理 #

9.1 数组展开 #

aql

FOR user IN users
    FOR hobby IN user.hobbies
        RETURN {
            user: user.name,
            hobby: hobby
        }

9.2 数组过滤 #

aql

FOR user IN users
    LET activeHobbies = (
        FOR hobby IN user.hobbies
            FILTER hobby != "游戏"
            RETURN hobby
    )
    RETURN {
        name: user.name,
        hobbies: activeHobbies
    }

9.3 数组转换 #

aql

FOR user IN users
    LET upperHobbies = (
        FOR hobby IN user.hobbies
            RETURN UPPER(hobby)
    )
    RETURN {
        name: user.name,
        hobbies: upperHobbies
    }

9.4 数组聚合 #

aql

FOR user IN users
    RETURN {
        name: user.name,
        hobbyCount: LENGTH(user.hobbies),
        firstHobby: FIRST(user.hobbies),
        lastHobby: LAST(user.hobbies)
    }

十、实战示例 #

10.1 用户活跃度分析 #

aql

LET activeThreshold = DATE_SUBTRACT(DATE_NOW(), 7, "days")
FOR user IN users
    LET recentActions = (
        FOR action IN userActions
            FILTER action.userId == user._key
            AND action.createdAt > activeThreshold
            RETURN action
    )
    LET actionCount = LENGTH(recentActions)
    LET activityLevel = actionCount > 10 ? "高" : 
                        actionCount > 5 ? "中" : 
                        actionCount > 0 ? "低" : "不活跃"
    RETURN {
        user: user.name,
        actionCount: actionCount,
        activityLevel: activityLevel
    }

10.2 销售排行榜 #

aql

FOR product IN products
    LET salesData = (
        FOR order IN orders
            FILTER order.productId == product._key
            COLLECT AGGREGATE
                totalSales = SUM(order.quantity),
                totalRevenue = SUM(order.amount)
            RETURN { totalSales, totalRevenue }
    )[0]
    RETURN {
        product: product.name,
        sales: salesData.totalSales || 0,
        revenue: salesData.totalRevenue || 0
    }
SORT revenue DESC
LIMIT 10

10.3 用户画像分析 #

aql

FOR user IN users
    LET profile = {
        basic: {
            age: user.age,
            city: user.city,
            gender: user.gender
        },
        behavior: (
            FOR action IN userActions
                FILTER action.userId == user._key
                COLLECT type = action.type WITH COUNT INTO count
                RETURN { type, count }
        ),
        preferences: (
            FOR order IN orders
                FILTER order.userId == user._key
                FOR product IN products
                    FILTER product._key == order.productId
                    COLLECT category = product.category WITH COUNT INTO count
                    SORT count DESC
                    LIMIT 3
                    RETURN { category, count }
        )
    }
    RETURN {
        user: user.name,
        profile: profile
    }

十一、性能优化 #

11.1 使用索引 #

javascript

db.users.ensureHashIndex(["status"]);
db.orders.ensureSkipList(["createdAt"]);

11.2 限制子查询结果 #

aql

FOR user IN users
    LET recentOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            SORT order.createdAt DESC
            LIMIT 5
            RETURN order
    )
    RETURN {
        user: user.name,
        recentOrders: recentOrders
    }

11.3 使用explain分析 #

javascript

db._explain(`
    FOR user IN users
        FILTER user.age > 25
        RETURN user
`);

十二、总结 #

高级查询要点：

子查询：嵌套查询处理复杂数据
CTE：使用LET定义公用表表达式
窗口函数：排名、累计计算
UNION：合并结果集
日期处理：日期范围、分组统计

下一步，让我们学习聚合与分组！