按分区估算男性和女性顾客 [英] Estimate male and female customers by partition

问题描述

我有五个表，其中包含有关银行详细信息的人工数据.

I have five tables that contain artificial data about bank details.

我的意图是准备两个最常用位置的查询，以进行男性客户和女性客户的交易.

My intention is to prepare query with 2 most often used locations for transactions for the male customers and for the female customers.

为此，我编写了以下查询:

In order to do this, I wrote this query:

SELECT c.Gender,
lo.Name, 
COUNT(c.Gender) OVER(PARTITION BY lo.Name) as TotalPerAccount
FROM dbo.Customer AS c
INNER JOIN dbo.Account AS a ON a.CustomerId=c.Id
INNER JOIN dbo.AccountDetails AS acc ON acc.AccountId=a.Id
INNER JOIN dbo.Location as lo ON lo.Id=acc.LocationId
ORDER BY c.Gender

但是上面的代码并不能给我很好的结果.实际上，我从每个位置都得到了一些结果.您可以在下面看到输出:

But above code don't give me good results. Actually I get some results from each location. You can see output below:

[！[在此处输入图片描述] [1]] [1]

[![enter image description here][1]][1]

此外，您还可以在这里看到会计科目表以及表之间的关系.

Also here you can see accounts diagram and how is look like relationship between tables.

[！[在此处输入图片描述] [2]] [2]

[![enter image description here][2]][2]

那么有人可以帮助我解决此问题吗?最终输出应如下图所示.

So can anybody help me how to fix this problem ? Final output should look like pic below.

[！[在此处输入图片描述] [3]] [3]

[![enter image description here][3]][3]

推荐答案

我没有您的数据集，但我相信以下内容将为您提供所需的结果:

I do not have your data set, but I believe the below would provide you with the desired result:

SELECT c.Gender,
lo.Name, 
COUNT(c.Gender) as TotalPerAccount
FROM dbo.Customer AS c
INNER JOIN dbo.Account AS a ON a.CustomerId=c.Id
INNER JOIN dbo.AccountDetails AS acc ON acc.AccountId=a.Id
INNER JOIN dbo.Location as lo ON lo.Id=acc.LocationId
GROUP BY c.Gender, lo.Name
ORDER BY c.Gender

编辑

如果您想看到最多的内容(例如前2名/前10名)，则可以这样实现:

If you want to see the most (like top 2 / Top 10) then you can achieve it like this:

SELECT TOP 10
c.Gender,
lo.Name, 
COUNT(c.Gender) as TotalPerAccount
FROM dbo.Customer AS c
INNER JOIN dbo.Account AS a ON a.CustomerId=c.Id
INNER JOIN dbo.AccountDetails AS acc ON acc.AccountId=a.Id
INNER JOIN dbo.Location as lo ON lo.Id=acc.LocationId
GROUP BY c.Gender, lo.Name
ORDER BY TotalPerAccount desc, c.Gender

这篇关于按分区估算男性和女性顾客的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！