问有可能自己参加询问不同的日期吗？
EN

Stack Overflow用户

提问于 2016-11-10 03:01:07

回答 2查看 943关注 0票数 0

试图搜索答案，阅读这样的文章：SQL Self-join with data comparison for different days，但不能完全理解这将如何在这种情况下。

希望得到任何帮助；

我有张桌子

UserID (号码)
UserType (string，显示它们是成员还是来宾)
sales_date (日期标记字段)
(再加上其他专栏，比如他们买的东西和我现在不感兴趣的物品的价格)

我正在尝试写一个查询，它将告诉我每个月有多少人在成为会员和客人之间。所以我可以回答这样的问题：“九月有多少人在这里，十月份又有多少人回来？”“9月份有多少人成为会员，但10月份被降级为客人？”“9月份有多少人是客人，但在10月份升级为会员？”

1:当需要从同一个表/同一个查询中要求两个不同的日期范围时，自我加入的方式是否合适？

他说:我想我需要UserID，然后是9月的UserType，10月份的UserType。这听起来对吗？不知道怎么要两个不同的日期

SELECT
      t1.UserID,
      t1.UserType as UserTypeSept,
      t2.UserType as UserTypeOct
   FROM 
      my_table t1
         join my_table t2
            on t1.UserID = t2.UserID
           AND t2.day > '2015-01-01' AND t2.day < '2015-02-01'
   where
      t1.day  >'2015-02-01' AND t1.day <'2015-03-01'
;

我在考虑沿着正确的车道吗？即使这样做有效，它也不会告诉我从9月到10月有多少人从“成员”变成了“客人”，但至少在两个不同的列中显示了他们的值。

谢谢

sql

google-bigquery

self-join

平台电商解决方案

为客户提供电商客服、电商图片系统识别及搜索的快速搭建方案

回答 2

Stack Overflow用户

发布于 2016-11-10 09:01:29

我建议使用分析函数，而不是更昂贵的自连接。您的数据适合用于窗口数据。请在查询下面运行，然后调整到您的表。您可能需要格式打印句点和用例子句，以便在接下来的几个月之间进行转换，比如“成员-来宾”到更有意义的名称。

      WITH
  members AS ( 
  SELECT 1 AS UserID, 'Member' AS UserType,  TIMESTAMP '2015-01-01' AS sales_date
  UNION ALL SELECT 1 AS UserID, 'Guest' AS UserType, TIMESTAMP '2015-02-01' AS sales_date 
  UNION ALL SELECT 2 AS UserID, 'Guest' AS UserType, TIMESTAMP '2015-01-01' AS sales_date
  UNION ALL SELECT 2 AS UserID, 'Member' AS UserType,TIMESTAMP '2015-02-01' AS sales_date
  UNION ALL SELECT 3 AS UserID, 'Guest' AS UserType, TIMESTAMP '2015-01-01' AS sales_date
  UNION ALL SELECT 3 AS UserID, 'Guest' AS UserType, TIMESTAMP '2015-02-01' AS sales_date
  UNION ALL SELECT 4 AS UserID, 'Guest' AS UserType, TIMESTAMP '2015-01-01' AS sales_date
  UNION ALL SELECT 4 AS UserID, 'Member' AS UserType,TIMESTAMP '2015-02-01' AS sales_date
  UNION ALL SELECT 5 AS UserID, 'Guest' AS UserType, TIMESTAMP '2016-07-01' AS sales_date 
  UNION ALL SELECT 5 AS UserID, 'Guest' AS UserType, TIMESTAMP '2016-08-01' AS sales_date
  UNION ALL SELECT 6 AS UserID, 'Member' AS UserType,TIMESTAMP '2016-03-01' AS sales_date
  UNION ALL SELECT 7 AS UserID, 'Guest' AS UserType, TIMESTAMP '2016-04-01' AS sales_date
  UNION ALL SELECT 7 AS UserID, 'Guest' AS UserType, TIMESTAMP '2016-05-01' AS sales_date
  UNION ALL SELECT 8 AS UserID, 'Guest' AS UserType, TIMESTAMP '2016-01-01' AS sales_date
  UNION ALL SELECT 8 AS UserID, 'Member' AS UserType,TIMESTAMP '2016-02-01' AS sales_date
  UNION ALL SELECT 9 AS UserID, 'Guest' AS UserType, TIMESTAMP '2016-01-03' AS sales_date
  UNION ALL SELECT 9 AS UserID, 'Member' AS UserType,TIMESTAMP '2016-02-06' AS sales_date)
SELECT
  COUNT(*),
  member,
  period,
  year
FROM (
  SELECT
    UserType,
    UserID,
    sales_date,
    FORMAT_DATE("%Y",DATE(sales_date)) AS year,
    CONCAT(
    FORMAT_DATE("%b",DATE(sales_date)),
    ' - ',
    FORMAT_DATE("%b", DATE(LEAD(sales_date,1) OVER (PARTITION BY userId ORDER BY sales_date ASC)))
    ) AS period,
    CONCAT(UserType,' - ', LEAD(UserType,1) OVER (PARTITION BY userId ORDER BY sales_date ASC)) AS member
  FROM
    members
  ORDER BY
    userid )
WHERE
  member IS NOT NULL
  and year = '2016'
GROUP BY
year,
  member,
  period

票数 0

Stack Overflow用户

发布于 2016-11-10 14:05:49

1:当需要从同一个表/同一个查询中要求两个不同的日期范围时，自我加入的方式是否合适？

不怎么有意思!那得看情况!在你的例子中--参见下面的第二条

他说:我想我需要UserID，然后是9月的UserType，10月份的UserType

我想下面做的都是你的期望。

请注意:它在每个月底查找UserType，并将其用作相应月份的用户类型。

/*
WITH my_table AS (
  SELECT 1 AS UserID, 'Member' AS UserType, TIMESTAMP '2015-09-01' AS sales_date UNION ALL
  SELECT 1 AS UserID, 'Member' AS UserType, TIMESTAMP '2015-09-02' AS sales_date UNION ALL
  SELECT 1 AS UserID, 'Member' AS UserType, TIMESTAMP '2015-09-03' AS sales_date UNION ALL
  SELECT 1 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-09-10' AS sales_date UNION ALL
  SELECT 1 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-10-01' AS sales_date UNION ALL
  SELECT 1 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-10-02' AS sales_date UNION ALL
  SELECT 2 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-09-01' AS sales_date UNION ALL
  SELECT 2 AS UserID, 'Member' AS UserType, TIMESTAMP '2015-10-01' AS sales_date UNION ALL
  SELECT 3 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-09-01' AS sales_date UNION ALL
  SELECT 3 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-10-01' AS sales_date UNION ALL
  SELECT 4 AS UserID,  'Guest' AS UserType, TIMESTAMP '2015-09-01' AS sales_date UNION ALL
  SELECT 4 AS UserID, 'Member' AS UserType, TIMESTAMP '2015-10-01' AS sales_date ) 
*/
SELECT 
  UserID,
  MAX(CASE WHEN sales_year_month = '2015-09' THEN UserTypeAtEndOfMonth END) AS UserTypeSept,
  MAX(CASE WHEN sales_year_month = '2015-10' THEN UserTypeAtEndOfMonth END) AS UserTypeOct
FROM (
  SELECT 
    UserID, 
    FORMAT_DATE('%Y-%m', DATE(sales_date)) AS sales_year_month,
    ARRAY_AGG(UserType ORDER BY sales_date DESC LIMIT 1)[OFFSET(0)] AS UserTypeAtEndOfMonth
  FROM my_table 
  GROUP BY 1, 2
)
GROUP BY 1