驾驶追踪APP：如何对数据库存储的用户旅程分数求和并计算平均值？

Hey there! I see you're trying to calculate the total and average of all user journey scores from your SQLite database, but having trouble with the sum function. Let's get this sorted out for you—there are two straightforward ways to tackle this, depending on your preference.

Option 1: Let SQLite Do the Heavy Lifting (Recommended) #

Instead of fetching all rows and calculating sum/average manually, let the database handle it directly with SQL aggregate functions. This is way more efficient, especially as your user base grows.

First, update or add a new database helper method to query the total and average:

public Cursor getScoreStats() {
    // Use getReadableDatabase() since we're only reading data
    SQLiteDatabase db = this.getReadableDatabase();
    // Replace `journey_score` with the actual name of your score column in userJourney table
    String query = "SELECT SUM(journey_score) AS total_score, AVG(journey_score) AS average_score FROM userJourney";
    return db.rawQuery(query, null);
}

Then, use this cursor in your activity/fragment to retrieve and display the values:

// Get the stats cursor from your DB helper
Cursor statsCursor = yourDbHelperInstance.getScoreStats();

if (statsCursor.moveToFirst()) {
    // Extract the total and average values
    double totalScore = statsCursor.getDouble(statsCursor.getColumnIndex("total_score"));
    double averageScore = statsCursor.getDouble(statsCursor.getColumnIndex("average_score"));
    
    // Display them in your UI (adjust the TextView IDs to match yours)
    totalScoreTextView.setText(String.format("Total Score: %.2f", totalScore));
    averageScoreTextView.setText(String.format("Average Score: %.2f", averageScore));
}

// Always close the cursor to prevent memory leaks
statsCursor.close();

Option 2: Calculate Sum/Average Manually (If You Need Raw Rows) #

If you still need to fetch all journey data for other purposes, you can calculate the sum and average by iterating through the cursor:

Cursor journeyCursor = yourDbHelperInstance.getJourneyScore();
double totalScore = 0.0;
int journeyCount = 0;

if (journeyCursor.moveToFirst()) {
    do {
        // Again, replace with your actual score column name
        double currentScore = journeyCursor.getDouble(journeyCursor.getColumnIndex("journey_score"));
        totalScore += currentScore;
        journeyCount++;
    } while (journeyCursor.moveToNext());
}

// Calculate average (avoid division by zero if there are no journeys)
double averageScore = journeyCount > 0 ? totalScore / journeyCount : 0.0;

// Display the results
totalScoreTextView.setText(String.format("Total Score: %.2f", totalScore));
averageScoreTextView.setText(String.format("Average Score: %.2f", averageScore));

journeyCursor.close();

Key Notes to Remember #

• Column Name Check: Double-check that journey_score matches the exact column name in your userJourney table—typos here are a super common culprit!
• Handle Empty Database: Always account for cases where there are no journey entries to avoid dividing by zero (like we did in Option 2).
• Cursor Management: Never forget to close your cursors after use to prevent memory leaks in your app.

内容的提问来源于stack exchange，提问作者D.Grosso