PostgreSQL查询：筛选字符串字段中目标单词首次出现的行

Got it, let's work through this problem step by step. You need to refine your initial query to not just exclude certain user agents, but also only keep rows where your specified target word (like Chrome, Safari, or Explorer) is the first one to appear in the user_agent field. Then you want the top 1000 rows ordered by id descending.

Core Requirements Recap #

• Exclude rows where user_agent contains "iPhone" or "Linux; Android"
• Only retain rows where your target word is the first occurrence among the list (Chrome, Safari, Explorer)
• Sort results by id in descending order, limit to 1000 rows

Solution Query #

Let's use Chrome as the target word for this example—you can swap it out for Safari/Explorer as needed:

SELECT *
FROM (
    SELECT 
        *,
        -- Calculate the first occurring target word in user_agent
        CASE
            -- Use LEAST to find the smallest position (earliest occurrence)
            WHEN LEAST(
                COALESCE(strpos(user_agent, 'Chrome'), 999999),
                COALESCE(strpos(user_agent, 'Safari'), 999999),
                COALESCE(strpos(user_agent, 'Explorer'), 999999)
            ) = strpos(user_agent, 'Chrome') AND strpos(user_agent, 'Chrome') != 0 THEN 'Chrome'
            WHEN LEAST(
                COALESCE(strpos(user_agent, 'Chrome'), 999999),
                COALESCE(strpos(user_agent, 'Safari'), 999999),
                COALESCE(strpos(user_agent, 'Explorer'), 999999)
            ) = strpos(user_agent, 'Safari') AND strpos(user_agent, 'Safari') != 0 THEN 'Safari'
            WHEN LEAST(
                COALESCE(strpos(user_agent, 'Chrome'), 999999),
                COALESCE(strpos(user_agent, 'Safari'), 999999),
                COALESCE(strpos(user_agent, 'Explorer'), 999999)
            ) = strpos(user_agent, 'Explorer') AND strpos(user_agent, 'Explorer') != 0 THEN 'Explorer'
            ELSE NULL
        END AS first_detected_word
    FROM user_logins
    -- Base exclusion filter
    WHERE user_agent NOT LIKE '%iPhone%'
      AND user_agent NOT LIKE '%Linux; Android%'
) filtered_logs
-- Only keep rows where the first detected word matches our target
WHERE first_detected_word = 'Chrome'
ORDER BY id DESC
LIMIT 1000;

Key Details Explained #

• Base Exclusion: The subquery's WHERE clause handles your initial requirement to filter out unwanted user agents.
• Position Calculation: strpos(user_agent, 'X') returns the starting index of the word in the string (0 if not found). We use COALESCE to replace 0 with a large number (999999) so non-existent words don't interfere with finding the earliest occurrence.
• First Occurrence Check: The CASE statement uses LEAST to identify which target word has the smallest position (appears first) and labels it as first_detected_word.
• Final Filter: We only keep rows where the first detected word matches our target (Chrome here).
• Sort & Limit: Finally, we order by id descending and grab the top 1000 rows.

Dynamic Reusable Version (Optional) #

If you want to reuse this query for different target words without rewriting it, use a parameter (works in PostgreSQL 12+ with PREPARE):

PREPARE filter_first_occurrence(text) AS
SELECT *
FROM (
    SELECT 
        *,
        CASE
            WHEN LEAST(
                COALESCE(strpos(user_agent, 'Chrome'), 999999),
                COALESCE(strpos(user_agent, 'Safari'), 999999),
                COALESCE(strpos(user_agent, 'Explorer'), 999999)
            ) = strpos(user_agent, $1) AND strpos(user_agent, $1) != 0 THEN $1
            ELSE NULL
        END AS first_detected_word
    FROM user_logins
    WHERE user_agent NOT LIKE '%iPhone%'
      AND user_agent NOT LIKE '%Linux; Android%'
) filtered_logs
WHERE first_detected_word = $1
ORDER BY id DESC
LIMIT 1000;

-- Run for Chrome
EXECUTE filter_first_occurrence('Chrome');

-- Run for Safari
EXECUTE filter_first_occurrence('Safari');

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