sqlite:用第一行的值更新组中的所有行 [英] sqlite: update all rows in group with value from first row

问题描述

目标 - 使用该用户的第一条记录中的值更新每个用户的所有记录

Goal - update all records for each user with a value from the first record for that user

背景 - 我有一个后期制作数据库(即我无法更改数据的捕获方式)，它是 12 个月内所有用户交互的日志.出于分析目的，我想用该用户首次出现在数据库中的月份 (yyyy-mm) 标记每一行.新字段 (first_use) 将来自该用户第一条记录中的现有字段 (month).

Background - I have a post-production database (i.e I can not change how data is captured) that is a log of all user interactions over 12 months. For analysis purposes, I want to tag each row with the month (yyyy-mm) of that user's very first appearance in the database. The new field (first_use) will come from existing field (month) in the first record for that user.

选择数据库中的第一行很简单:

Selecting the first row in the database is the easy part:

SELECT month,user_email, MIN(month) as first_month
    FROM table
    GROUP BY user_email 

这为我提供了表格中每个用户的第一行(第一笔交易).

This gives me the first row (first transaction) for each user in the table.

我的问题是如何编写 UPDATE 语句.当我尝试这样的事情时:

My question is how to write the UPDATE statement. When I try something like this:

UPDATE table set first_use = (
SELECT MIN(month) as first_month
    FROM table
    GROUP BY user_email)

那么所有 first_use 字段都包含相同的日期(表中最早的日期).如何让更新应用到一个组中的所有记录，一次一个组，该组的月份是正确的?

then all first_use fields contain the same date (the earliest date in the table). How do I get the update to apply to all records in a group, one group at a time with the right month for that group?

推荐答案

您需要通过以下方式缩小您的群组:

YOu need to narrow your group by:

UPDATE table set first_use = (
    SELECT MIN(month) as first_month
    FROM table T2
    where table.user_email = T2.user_email
    ) 

您不需要在子查询中显式分组.但这是一个缓慢的更新，因为它将为表中的每个元组运行一个子查询.

You don't need to explicitly group by in the subquery. But it is a slow update, since it will run a subquery for every tuple in table.

这篇关于sqlite:用第一行的值更新组中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！