Neo4j百分比查询作业求助：按交易用途统计占比

Hey there! Let's work through this together to figure out how to calculate those trade purpose percentages you need. From what you described, your data has taxonomic nodes (Taxon, Class, Order, etc.) linked to transaction records that include import/export status and trade purposes like breeding, capture, wild, zoo, etc. Here's a step-by-step breakdown with Cypher queries tailored to this:

1. Start with a Basic Usage Count #

First, let's get a clear picture of how many transactions fall under each purpose. This query will group all transactions by their purpose and count them up:

MATCH (tx:Transaction)
RETURN tx.trade_purpose AS trade_purpose, COUNT(*) AS total_transactions
ORDER BY total_transactions DESC

Note: Replace tx.trade_purpose with the actual property name for trade purpose in your data (e.g., tx.use or tx.purpose).

2. Calculate Overall Purpose Percentages #

To get the percentage each purpose makes up of all transactions, we first need the total number of transactions, then divide each purpose's count by that total:

// First, get the total number of all transactions
MATCH (tx:Transaction)
WITH COUNT(*) AS total_all_trades

// Then calculate percentages for each purpose
MATCH (tx:Transaction)
RETURN tx.trade_purpose AS trade_purpose,
       COUNT(*) AS total_transactions,
       ROUND((COUNT(*) * 100.0 / total_all_trades), 2) AS percentage_of_total
ORDER BY percentage_of_total DESC

The ROUND(..., 2) ensures your percentages are clean with two decimal places, and using 100.0 instead of 100 avoids integer division errors.

3. Filter by Taxonomic Group (e.g., Birds/Aves) #

If you want to narrow this down to a specific group—like calculating purpose percentages only for bird (Aves) import transactions—we'll link the Transaction node to the relevant Class node:

// Get total transactions for the Aves class
MATCH (c:Class {name: 'Aves'})<-[:BELONGS_TO]-(tx:Transaction)
WHERE tx.import_export_status = 'Import' // Add this line if you want to filter by import/export
WITH COUNT(tx) AS total_aves_trades

// Calculate percentages for each purpose in Aves
MATCH (c:Class {name: 'Aves'})<-[:BELONGS_TO]-(tx:Transaction)
WHERE tx.import_export_status = 'Import'
RETURN tx.trade_purpose AS trade_purpose,
       COUNT(tx) AS aves_transactions,
       ROUND((COUNT(tx) * 100.0 / total_aves_trades), 2) AS percentage_of_aves_trades
ORDER BY percentage_of_aves_trades DESC

Adjust the relationship type ([:BELONGS_TO]) and property names (name, import_export_status) to match your actual data model.

4. Compare Across All Taxonomic Groups #

If you want to see purpose percentages for every class (or order/family) at once, this query will group results by taxonomic group and purpose:

MATCH (c:Class)<-[:BELONGS_TO]-(tx:Transaction)
WITH c.name AS class_name, tx.trade_purpose AS purpose, COUNT(tx) AS purpose_count
WITH class_name, 
     COLLECT({purpose: purpose, count: purpose_count}) AS purpose_details,
     SUM(purpose_count) AS total_class_trades

UNWIND purpose_details AS pd
RETURN class_name,
       pd.purpose AS trade_purpose,
       pd.count AS transactions,
       ROUND((pd.count * 100.0 / total_class_trades), 2) AS percentage
ORDER BY class_name, percentage DESC

This will give you a row for each class-purpose combination, showing how much each purpose contributes to that class's total transactions.

Just remember to tweak all node labels, relationship types, and property names to match exactly what's in your Neo4j database—if your transaction node is called Trade instead of Transaction, or the purpose property is usage, swap those out accordingly!

内容的提问来源于stack exchange，提问作者HorseGirl