是否有一种方法来提取年数据从日期在ORACLE?统计用户投票

Question

嗨，我正在创建一个数据库，用于跟踪用户对我们的产品投票的次数。

是否有一种方法，我可以改变下面的查询，以计算每年的最大票数?我正在使用Oracle SQL developer

创建表的结构为

Members(username, email, pwd) 
Votes (username*, prodCode*, score, voteDate)

注意:

SELECT username, count(username)
FROM Votes NATURAL JOIN members
GROUP BY username, email
HAVING COUNT(*) >= All(SELECT count(username)
                   FROM Votes v1
                   GROUP BY username);

Answer 1

我不完全确定我理解你的问题，但这样的东西也许?

SELECT username, 
       extract(year from voteDate) as vote_year, 
       count(*) as votes_per_year,
       max(count(*)) over (partition by extract(year from voteDate)) as max_votes_per_year
FROM Votes vt
GROUP BY username, extract(year from voteDate);

这将为您提供每个用户和年份的计数以及每行中每年的最大计数。(注意，这里不需要成员表，因为显然不需要其中的任何列)。

如果您需要显示没有人投票的年份(或某个特定用户没有投票)的零计数，那么您需要这样的内容:

with years as (
   select 2009 + level as year
   from dual
   connect by level <= 21
)  
SELECT username, 
       y.year, 
       count(vt.username) as votes_per_year,
       max(count(vt.username)) over (partition by y.year) as max_votes_per_year
FROM years y 
  LEFT JOIN Votes vt on y.year = extract(year from vt.voteDate)
GROUP BY username, y.year
order by y.year, username;

这将使用公共表表达式(with ...)"实时"生成2010年到2030年之间的年份列表。您可以调整CTE以延长或缩小年数。

Answer 2

为此，您最好有一个年份表(或者至少有一个数字表，可以帮助您设置一组年份)。

SELECT
    username
    , MAX(cnt) AS mcnt
FROM (
    SELECT
        username
        , count(username) AS cnt
    FROM members 
    CROSS JOIN Years
    INNER JOIN Votes ON (
        members.username = Votes.username -- using an INT here would be more efficient
        AND
        Votes.voteDate >= Years.firstJanuary
        AND
        Votes.voteDate < Years.firstJanuary+ 1 Year -- Pseudo code, you didn't mention any DBMS
    )
    GROUP BY username, prodCode, Years.firstJanuary
) AS X