SQL实现相近行合并：用户搜索日志完整查询聚合需求

Got it, let's walk through how to turn those messy incremental search logs into clean, report-ready query counts. Here's a step-by-step approach that works perfectly with your sample data:

Step 1: Parse Raw Logs into Structured Data #

First, we need to convert each unstructured log line into a structured format so we can easily work with key fields like timestamp, user ID, session ID, and query text. Each line includes:

• Duplicate date (we’ll ignore one)
• Timestamp
• User ID (cid)
• Unique session UUID (identifies a single search attempt)
• Search flags
• The actual query text (everything after "search query ")

Here’s a Python snippet to parse each line:

def parse_log_line(line):
    parts = line.strip().split()
    # Extract timestamp (skip duplicate date)
    timestamp = " ".join(parts[1:3])
    cid = parts[3]
    session_id = parts[4]
    # Grab the full query text
    query_start_idx = parts.index("search query") + 2
    query_text = " ".join(parts[query_start_idx:])
    return {
        "timestamp": timestamp,
        "cid": cid,
        "session_id": session_id,
        "query": query_text,
        "query_length": len(query_text)
    }

# Example usage with your log lines
logs = [
    "2019-12-09 2019-12-09 12:58:45 5dea585477c94502b52c43fb 92cd6cef-3ed8-4416-ac2d-cc347780b976 search 1 search query vacuum cleaner",
    # ... add all other log lines here ...
]
parsed_logs = [parse_log_line(line) for line in logs]

Step 2: Group Related Search Entries #

Each chain of incremental inputs belongs to a single user’s search session (marked by the unique UUID in each line). We’ll group logs by combining the user ID (cid) and session ID—this ensures we only group inputs from one specific search attempt.

If session IDs weren’t available, we could group by user ID and check if consecutive timestamps are within 2 seconds, but using the session ID is far more reliable (like in your sample data, each search sequence has its own UUID).

Step 3: Extract the Final, Complete Query #

For each group, the longest query (or the latest timestamp, since incremental inputs get longer over time) is the "complete" search we want to report. Both methods will give the same result in most cases.

Here’s how to pull the final query per group:

from collections import defaultdict

# Group logs by (user ID, session ID)
grouped_logs = defaultdict(list)
for log in parsed_logs:
    key = (log["cid"], log["session_id"])
    grouped_logs[key].append(log)

# Grab the longest query from each group
final_queries = []
for group in grouped_logs.values():
    # Sort by query length descending, take the first entry
    sorted_group = sorted(group, key=lambda x: x["query_length"], reverse=True)
    final_queries.append(sorted_group[0]["query"])

Step 4: Count Query Occurrences #

Finally, we count how many times each complete query appears to get our report-ready results:

from collections import Counter

query_counts = Counter(final_queries)

# Print the results (matches your expected output)
for query, count in query_counts.items():
    print(f"{query} {count}")

Output for Your Sample Data: #

vacuum cleaner 1
blue widget 1
widget 1

Key Notes #

• Session ID vs Timestamp Intervals: Using session IDs is more accurate than relying solely on 2-second gaps, but if session IDs are missing, adjust grouping to check if time differences between consecutive user logs are ≤2 seconds.
• Edge Cases: If a user stops typing mid-query, the longest input still represents the most complete search they initiated.
• Scalability: For large datasets, use tools like Pandas or Spark to handle grouping and counting efficiently instead of pure Python loops.

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