与表中的其他行比较 [英] Comparing with other rows in table

问题描述

他的杰出思想家

我想创建一个CASE条件，以便对active_users(如果存在)在60天内存在该条件给出是"，对于同一个onymous_id，则为最近的uuid_ts.

I want to create a CASE condition to give me a "yes" for active_users that is if there exists within 60 days, a more recent uuid_ts for the same anonymous_id.

SELECT  t1.anonymous_id user_id,
        t1.uuid_ts activity_date, 
        t2.uuid_ts signup_date,

        -- Activity Lifetime: difference of number of days signed up to last activity
        DATE_DIFF(CAST(t2.uuid_ts AS DATE), CAST(t1.uuid_ts AS DATE), DAY) AS activity_lifetime,

        -- New Users: If month of activity is same as sign_up month
        (CASE WHEN DATE_DIFF(CAST(t1.uuid_ts AS DATE), CAST(t2.uuid_ts AS DATE), MONTH)=0 THEN TRUE ELSE FALSE END) AS new_user,

        -- Active Users: If month of activity is greater than sign_up month AND activity is found 
        (CASE WHEN DATE_DIFF(CAST(t1.uuid_ts AS DATE), CAST(t2.uuid_ts AS DATE), MONTH)>0 
        -- ** ____ NEED HELP HERE ____ **
         AND anonymous_id NOT IN (SELECT anonymous_id FROM datascience.last_user_activity)
         AND DATE_ADD(activity_date, INTERVAL 60 DAY) > (S)


FROM datascience.last_user_activity AS t1
INNER JOIN datascience.full_signup_completed AS t2
ON t2.anonymous_id = t1.anonymous_id
WHERE DATE(t1.uuid_ts) IS NOT NULL AND DATE(t2.uuid_ts) IS NOT NULL
ORDER BY activity_lifetime DESC

样本数据:

anon_id|signup_date|activity_date|
__________________________________
123    |01-01-2019 |02-01-2019   | 
123    |01-01-2019 |02-02-2019   | 
123    |01-01-2019 |02-03-2019   | 
123    |01-01-2019 |02-04-2019   |

想要:

anon_id|signup_date|activity_date| active
__________________________________
123    |01-01-2019 |02-01-2019   | yes
123    |01-01-2019 |02-02-2019   | yes
123    |01-01-2019 |02-03-2019   | no
123    |01-01-2019 |02-04-2019   | no

如果同一行中存在未来日期(在60天范围内)，则活动字段显示是"，否则显示否".

if a future date exists in the same row, within the range of 60 days, then the field active shows "yes", else a "no".

推荐答案

仍然不能100％地确定这是您要寻找的东西，但希望对您有所帮助:

Still not 100% sure this is what you are looking for, but I hope it helps:

在60天内:

(自2019年2月4日开始> 2019年2月3日且在60天内输出为是，是，是，否")

(The output would be "yes, yes, yes, no" since 02-04-2019 > 02-03-2019 and within 60 days)

WITH
  sample_data AS (
  SELECT
    '123' AS anon_id,    DATE('2019-01-01') AS signup_date,
    DATE('2019-01-02') AS activity_date
  UNION ALL
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-02-02') AS activity_date
  UNION ALL
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-03-02') AS activity_date
  UNION ALL
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-04-02') AS activity_date)
SELECT
  anon_id,
  signup_date,
  activity_date,
  (CASE
      WHEN EXISTS( SELECT 'found' FROM sample_data t2 WHERE t2.anon_id = t1.anon_id AND t2.activity_date > t1.activity_date AND t2.activity_date <= DATE_ADD(t1.activity_date, INTERVAL 60 DAY)) THEN 'yes'
    ELSE
    'no'
  END
    ) AS active
FROM
  sample_data t1
ORDER BY 1,2,3

60天或以后:

(输出将是是，否，否，否"，因为2月有28天和3月31日，所以在02-02-2019和02-04-2019之间有59天)

(The output would be "yes, no, no, no" since February has 28 days and March 31, so between 02-02-2019 and 02-04-2019 there are 59 days)

WITH
  sample_data AS (
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-01-02') AS activity_date
  UNION ALL
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-02-02') AS activity_date
  UNION ALL
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-03-02') AS activity_date
  UNION ALL
  SELECT
    '123' AS anon_id,
    DATE('2019-01-01') AS signup_date,
    DATE('2019-04-02') AS activity_date)
SELECT
  anon_id,
  signup_date,
  activity_date,
  (CASE
      WHEN EXISTS( SELECT 'found' FROM sample_data t2 WHERE t2.anon_id = t1.anon_id AND t2.activity_date >= DATE_ADD(t1.activity_date, INTERVAL 60 DAY)) THEN 'yes'
    ELSE
    'no'
  END
    ) AS active
FROM
  sample_data t1
ORDER BY 1,2,3

这篇关于与表中的其他行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！