如何创建含6字段的DataTable存储千余用户90天日常数据？

Hey there! Let's walk through a super beginner-friendly solution for your 1000+ user site. I'll go with a Python + SQLite + DataTables.js setup because it’s easy to get started with, no fancy server config needed, and handles your 90-day daily data needs perfectly.

1. Define Your 6 Fields (Customizable Example) #

First, let’s lock in what your 6 fields should be—here’s a practical template you can adjust based on your actual user data needs:

• user_id: Unique ID for each user (links to your existing user table, required)
• record_date: The date of the daily record (required, ensures we track one entry per user per day)
• daily_active_minutes: Total minutes the user was active on your site that day
• page_views: Number of pages the user viewed that day
• action_count: Number of key actions (like posts, comments, clicks) the user took
• status: Daily user status (e.g., active if they logged in, inactive otherwise)

2. Create the Database Table (SQLite, No Extra Setup) #

SQLite is a file-based database, so you don’t need to install or run a separate server—perfect for beginners. Use Python’s built-in sqlite3 library to create your table:

import sqlite3

# Connect to database (creates the file if it doesn't exist)
conn = sqlite3.connect('user_daily_data.db')
cursor = conn.cursor()

# Create the daily metrics table
create_table_query = '''
CREATE TABLE IF NOT EXISTS user_daily_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    record_date DATE NOT NULL,
    daily_active_minutes INTEGER DEFAULT 0,
    page_views INTEGER DEFAULT 0,
    action_count INTEGER DEFAULT 0,
    status TEXT DEFAULT 'inactive',
    -- Prevent duplicate entries for the same user on the same day
    UNIQUE(user_id, record_date)
);
'''

cursor.execute(create_table_query)
conn.commit()
conn.close()

That UNIQUE(user_id, record_date) constraint is crucial—it stops you from accidentally adding multiple entries for the same user on the same day.

3. Insert/Update Daily Data #

You can run a daily script (or update data in real-time as users interact) to add or refresh user metrics. Here’s a simple Python function for this:

import sqlite3
from datetime import date

def update_user_daily_stats(user_id, active_mins, page_views, action_count):
    conn = sqlite3.connect('user_daily_data.db')
    cursor = conn.cursor()
    today = date.today()
    
    # Use REPLACE to update existing records or insert new ones
    query = '''
    REPLACE INTO user_daily_metrics (user_id, record_date, daily_active_minutes, page_views, action_count, status)
    VALUES (?, ?, ?, ?, ?, ?)
    '''
    # Automatically set status based on active minutes
    status = 'active' if active_mins > 0 else 'inactive'
    cursor.execute(query, (user_id, today, active_mins, page_views, action_count, status))
    
    conn.commit()
    conn.close()

# Example usage: User 123 was active 30 mins, viewed 15 pages, took 5 actions today
update_user_daily_stats(123, 30, 15, 5)

4. Display Data with DataTables.js (Frontend) #

If you want to view this data in your site’s admin area, DataTables.js is a great pick—it adds sorting, pagination, and search out of the box, which works perfectly for 90k total records (1000 users × 90 days).

How to Set It Up: #

1. Download jQuery and DataTables.js (get the compressed versions from their official sources) and place the files in your project folder.
2. Create a simple HTML page to display the table:

<!DOCTYPE html>
<html>
<head>
    <title>User Daily Metrics</title>
    <!-- Link to local jQuery and DataTables files -->
    <script src="jquery.min.js"></script>
    <link rel="stylesheet" href="jquery.dataTables.min.css">
    <script src="jquery.dataTables.min.js"></script>
</head>
<body>
    <h2>90-Day User Daily Data</h2>
    <table id="userDataTable" class="display" style="width:100%">
        <thead>
            <tr>
                <th>User ID</th>
                <th>Record Date</th>
                <th>Active Minutes</th>
                <th>Page Views</th>
                <th>Action Count</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody>
            <!-- Replace with dynamic data from your database -->
            <!-- Example static row -->
            <tr>
                <td>123</td>
                <td>2024-05-20</td>
                <td>30</td>
                <td>15</td>
                <td>5</td>
                <td>active</td>
            </tr>
            <!-- Add more rows as needed -->
        </tbody>
    </table>

    <script>
        $(document).ready(function() {
            // Initialize DataTables with basic settings
            $('#userDataTable').DataTable({
                pageLength: 10, // Show 10 rows per page
                searching: true, // Enable search bar
                ordering: true // Allow sorting by columns
            });
        });
    </script>
</body>
</html>

For dynamic data, you can use a simple Python web framework like Flask to pull data from the database and render it into the table—this is easy to learn once you’ve got the basics down.

5. Query 90-Day Data for a User #

Need to pull a specific user’s 90-day history? Here’s a Python function to do that:

import sqlite3
from datetime import date, timedelta

def get_user_90day_history(user_id):
    conn = sqlite3.connect('user_daily_data.db')
    cursor = conn.cursor()
    ninety_days_ago = date.today() - timedelta(days=90)
    
    query = '''
    SELECT record_date, daily_active_minutes, page_views, action_count, status
    FROM user_daily_metrics
    WHERE user_id = ? AND record_date >= ?
    ORDER BY record_date DESC
    '''
    cursor.execute(query, (user_id, ninety_days_ago))
    data = cursor.fetchall()
    
    conn.close()
    return data

# Get user 123's last 90 days of data
user_history = get_user_90day_history(123)
for entry in user_history:
    print(entry)

Quick Tips for Scaling #

• SQLite handles 90k records easily, but if your user count grows to 10k+, consider migrating to MySQL or PostgreSQL—most of your SQL code will work the same.
• Use a daily cron job (Linux) or Task Scheduler (Windows) to run your data update script automatically.
• Back up your SQLite database file regularly to avoid data loss.

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