SQL查询具有带有二进制指示符和时间差的新列

Question

我正试图从表1中编写sql查询，以获得与表2类似的结果，但未能成功。任何建议都将不胜感激。

我有一个如下的表格1：

+--------+--------------+----------+---------------+-----------------------+
| UserID | account_type |  Region  | account_Level | Level_assessment_date |
+--------+--------------+----------+---------------+-----------------------+
|   1234 | A            | west268  | Normal        | Jul-17                |
|   1234 | A            | west268  | platinum      | Jul-18                |
|   2342 | B            | north234 | Normal        | May-15                |
|   3565 | A            | west268  | Normal        | Jun-14                |
|   3565 | B            | west268  | Normal        | May-17                |
|   5678 | A            | west268  | platinum      | Sep-15                |
|   6689 | A            | north234 | Normal        | Oct-16                |
|   6689 | B            | north234 | platinum      | Jan-18                |
+--------+--------------+----------+---------------+-----------------------+

我想查询表1以获得如下表2：

+--------+--------------+----------+---------------+-----------------------+----------------------+----------------------+
| UserID | account_type |  Region  | account_Level | Level_assessment_date | upgraded_to_platinum | Time_to_upgrade_DAYS |
+--------+--------------+----------+---------------+-----------------------+----------------------+----------------------+
|   1234 | A            | west268  | Normal        | Jul-17                | Y                    | 365                  |
|   2342 | B            | north234 | Normal        | May-15                | N                    | N/A                  |
|   3565 | A            | west268  | Normal        | Jun-14                | N                    | N/A                  |
|   3565 | B            | west268  | Normal        | May-17                | N                    | N/A                  |
|   6689 | A            | north234 | Normal        | Oct-16                | N                    | N/A                  |
+--------+--------------+----------+---------------+-----------------------+----------------------+----------------------+

记录正常的每个用户帐户级别，二进制(是/否(列升级为白金表示帐户级别是否已从正常升级为白金。和列Time_to_upgrade_DAYS从正常到白金的天数(如果可用(

Answer 1

如果您有sql server，可以尝试一下。

DECLARE @table1  TABLE ( UserID INT, account_type VARCHAR(10),  Region  VARCHAR(10), account_Level VARCHAR(10), Level_assessment_date VARCHAR(10))
INSERT INTO @table1  VALUES
(1234, 'A', 'west268 ', 'Normal  ', 'Jul-17'),
(1234, 'A', 'west268 ', 'platinum', 'Jul-18'),
(2342, 'B', 'north234', 'Normal  ', 'May-15'),
(3565, 'A', 'west268 ', 'Normal  ', 'Jun-14'),
(3565, 'B', 'west268 ', 'Normal  ', 'May-17'),
(5678, 'A', 'west268 ', 'platinum', 'Sep-15'),
(6689, 'A', 'north234', 'Normal  ', 'Oct-16'),
(6689, 'B', 'north234', 'platinum', 'Jan-18')
SELECT T1.*, 
CASE WHEN T2.UserID IS NOT NULL THEN 'Y' ELSE 'N' END upgraded_to_platinum, 
DATEDIFF(DAY,'01-'+T1.Level_assessment_date, '01-'+T2.Level_assessment_date) Time_to_upgrade_DAYS
FROM @table1 T1
LEFT JOIN @table1 T2 ON T1.UserID = T2.UserID and T1.account_type = T2.account_type and T2.account_Level ='platinum'
WHERE 
T1.account_Level = 'Normal'

结果：

UserID      account_type Region     account_Level Level_assessment_date upgraded_to_platinum Time_to_upgrade_DAYS
----------- ------------ ---------- ------------- --------------------- -------------------- --------------------
1234        A            west268    Normal        Jul-17                Y                    365
2342        B            north234   Normal        May-15                N                    NULL
3565        A            west268    Normal        Jun-14                N                    NULL
3565        B            west268    Normal        May-17                N                    NULL
6689        A            north234   Normal        Oct-16                N                    NULL

Answer 2

SQL演示

SELECT "UserID", "account_type",
MAX("Region") as Region,
MAX("account_Level") as account_Level,
MAX(CASE WHEN "account_Level" = 'platinum' 
THEN 'yes'
ELSE 'no'
END) as upgraded_to_platinum,
MAX(CASE WHEN "account_Level" = 'platinum' THEN "Level_assessment_date" END) -
MAX(CASE WHEN "account_Level" <> 'platinum' THEN "Level_assessment_date" END) as Time_to_upgrade_DAYS
FROM Table1
GROUP BY "UserID", "account_type"
ORDER BY "UserID", "account_type"

输出

| UserID | account_type |   region | account_level | upgraded_to_platinum |                             time_to_upgrade_days |
|--------|--------------|----------|---------------|----------------------|--------------------------------------------------|
|   1234 |            A |  west268 |      platinum |                  yes | 0 years 0 mons 365 days 0 hours 0 mins 0.00 secs |
|   2342 |            B | north234 |        Normal |                   no |                                           (null) |
|   3565 |            A |  west268 |        Normal |                   no |                                           (null) |
|   3565 |            B |  west268 |        Normal |                   no |                                           (null) |
|   5678 |            A |  west268 |      platinum |                  yes |                                           (null) |
|   6689 |            A | north234 |        Normal |                   no |                                           (null) |
|   6689 |            B | north234 |      platinum |                  yes |                                           (null) |