有可能自己参加询问不同的日期吗？
有可能自己参加询问不同的日期吗？
提问于 2016-11-10 11:01:07
试图搜索答案，阅读这样的文章：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月有多少人从“成员”变成了“客人”，但至少在两个不同的列中显示了他们的值。
谢谢
回答 2
Stack Overflow用户
发布于 2016-11-10 17: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,
periodStack Overflow用户
发布于 2016-11-10 22: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如果要对示例数据进行测试，可以删除注释。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40525971
复制相关文章
相似问题
腾讯云开发者
