SQL服务器查询

发布于 2024-09-02 04:01:39 字数 903 浏览 2 评论 0原文

我有一个 SQL Server 数据库，其中包含一个注册表，我需要将其绘制在图表上。问题是我需要根据用户注册的位置（例如网站、wap 网站或移动应用程序）来细分。

生成的输出数据应如下所示...

[date] [num_reg_website] [num_reg_wap_site] [num_reg_mobileapp]
1 FEB 2010,24,35,64
2 FEB 2010,23,85,48
3 FEB 2010,29,37,79

等...

源表如下...

UUID(int), signupdate(datetime), requestsource(varchar(50))

该表中的一些简单数据如下所示...

1001,2010-02-2:00:12:12,'website'
1002,2010-02-2:00:10:17,'app'
1003,2010-02-3:00:14:19,'website'
1004,2010-02-4:00:16:18,'wap'
1005,2010-02-4:00:18:16,'website'

运行以下查询会返回网站注册的一个数据列“总注册数”，但不幸的是，我不确定如何对多个列执行此操作......

select  CAST(FLOOR(CAST([signupdate]AS FLOAT ))AS DATETIME) as [signupdate], count(UUID)  as 'total registrations' FROM [UserRegistrationRequests] WHERE requestsource = 'website'
group by CAST(FLOOR(CAST([signupdate]AS FLOAT ))AS DATETIME)

原文

I have a SQL Server DB containing a registrations table that I need to plot on a graph over time.
The issue is that I need to break this down by where the user registered from (e.g. website, wap site, or a mobile application).

the resulting output data should look like this...

[date] [num_reg_website] [num_reg_wap_site] [num_reg_mobileapp]
1 FEB 2010,24,35,64
2 FEB 2010,23,85,48
3 FEB 2010,29,37,79

etc...

The source table is as follows...

UUID(int), signupdate(datetime), requestsource(varchar(50))

some smple data in this table looks like this...

1001,2010-02-2:00:12:12,'website'
1002,2010-02-2:00:10:17,'app'
1003,2010-02-3:00:14:19,'website'
1004,2010-02-4:00:16:18,'wap'
1005,2010-02-4:00:18:16,'website'

Running the following query returns one data column 'total registrations' for the website registrations but I'm not sure how to do this for multiple columns unfortunatly....

select  CAST(FLOOR(CAST([signupdate]AS FLOAT ))AS DATETIME) as [signupdate], count(UUID)  as 'total registrations' FROM [UserRegistrationRequests] WHERE requestsource = 'website'
group by CAST(FLOOR(CAST([signupdate]AS FLOAT ))AS DATETIME)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

左耳近心 2024-09-09 04:01:39

SELECT  CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),
        SUM(CASE WHEN requestsource = 'website' THEN 1 ELSE 0 END),
        SUM(CASE WHEN requestsource = 'wap' THEN 1 ELSE 0 END),
        SUM(CASE WHEN requestsource = 'app' THEN 1 ELSE 0 END)
FROM    mytable
GROUP BY
        CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

SELECT  CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),
        SUM(CASE WHEN requestsource = 'website' THEN 1 ELSE 0 END),
        SUM(CASE WHEN requestsource = 'wap' THEN 1 ELSE 0 END),
        SUM(CASE WHEN requestsource = 'app' THEN 1 ELSE 0 END)
FROM    mytable
GROUP BY
        CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

回复收藏 0 原文

飘落散花 2024-09-09 04:01:39

听起来您遇到了两个问题。第一个是如何将 DateTime 转换为仅日期值。由于您没有指定 SQL Server 的版本，我假设它是 SQL Server 2005 及更早版本。为此，我通常使用 DateDiff 技巧，找到从第 0 天开始的天数，然后将其转换为 DateTime。另一个技巧是创建类似输出的交叉表。

Select Cast(DateDiff(d, 0, [signupdate]) As DateTime) As [Date]
    , Sum( Case When requestsource = 'website' Then 1 Else 0 End ) As num_reg_website
    , Sum( Case When requestsource = 'wap' Then 1 Else 0 End ) As num_reg_wap_site
    , Sum( Case When requestsource = 'mobileapp' Then 1 Else 0 End ) As num_reg_mobileapp
From [UserRegistrationRequests]
Group By Cast(DateDiff(d, 0, [signupdate]) As DateTime)

如果您使用的是 SQL Server 2008，则可以利用其 Date 数据类型并执行以下操作：

Select Cast([signupdate]) As Date) As [Date]
    , Sum( Case When requestsource = 'website' Then 1 Else 0 End ) As num_reg_website
    , Sum( Case When requestsource = 'wap' Then 1 Else 0 End ) As num_reg_wap_site
    , Sum( Case When requestsource = 'mobileapp' Then 1 Else 0 End ) As num_reg_mobileapp
From [UserRegistrationRequests]
Group By Cast([signupdate]) As Date)

It sounds like there are two problems you are having. The first is how to convert the DateTime into a Date only value. Since you did not specify which version of SQL Server, I'm assuming it is SQL Server 2005 and prior. For that I typically use the DateDiff trick where I find the number of days from day zero and then cast that as a DateTime. The other trick is creating a crosstab like output.

Select Cast(DateDiff(d, 0, [signupdate]) As DateTime) As [Date]
    , Sum( Case When requestsource = 'website' Then 1 Else 0 End ) As num_reg_website
    , Sum( Case When requestsource = 'wap' Then 1 Else 0 End ) As num_reg_wap_site
    , Sum( Case When requestsource = 'mobileapp' Then 1 Else 0 End ) As num_reg_mobileapp
From [UserRegistrationRequests]
Group By Cast(DateDiff(d, 0, [signupdate]) As DateTime)

If you are using SQL Server 2008, you can take advantage of its Date data type and do something like:

Select Cast([signupdate]) As Date) As [Date]
    , Sum( Case When requestsource = 'website' Then 1 Else 0 End ) As num_reg_website
    , Sum( Case When requestsource = 'wap' Then 1 Else 0 End ) As num_reg_wap_site
    , Sum( Case When requestsource = 'mobileapp' Then 1 Else 0 End ) As num_reg_mobileapp
From [UserRegistrationRequests]
Group By Cast([signupdate]) As Date)

回复收藏 0 原文

骑趴 2024-09-09 04:01:39

尝试使用PIVOT

回复收藏 0 原文

岁月如刀 2024-09-09 04:01:39

您可以按这两个值进行分组；首先，日期（正如您所做的那样，尽管您可以使用 CONVERT(varchar(50), signupdate, 102) 或其他某种样式，以避免所有这些地板:)），然后注册方式。 GROUP BY 可以带多个参数！

所以，就像：

SELECT requestsource, count(*), CONVERT(varchar(50), signupdate, 102)
FROM userregistrationrequest
GROUP BY CONVERT(varchar(50), signupdate, 102), requestsource

......用你自己的风格而不是 102 来适应。

有关合适 CONVERT 样式的列表，请参阅此处。

You could GROUP BY the two values; first, the date (as you're doing, although you could use a CONVERT(varchar(50), signupdate, 102) or some other style, to avoid all that flooring :) ), and then the mode of signup. GROUP BY can take multiple parameters!

So, something like:

SELECT requestsource, count(*), CONVERT(varchar(50), signupdate, 102)
FROM userregistrationrequest
GROUP BY CONVERT(varchar(50), signupdate, 102), requestsource

... with your own style rather than 102 to suit.

See here for a list of suitable CONVERT styles.

回复收藏 0 原文

~没有更多了~