基于日期的 SQL 排名 [英] SQL Rank based on date

问题描述

我正在尝试根据过去 18 个月内的访问次数将客户与首选"商家相关联，决胜局是最近的访问日期.我的决胜局有点麻烦.如果有两条记录都根据某个 MemberID 的访问次数排名为 1，我想将 IsFirst 位列设置为 1，该记录的 MAX(EncounterDate) 为该 MemberID.我应该怎么做?

I am trying to link a customer to a "peferred" merchant based on number of visits within the last 18 months, with the tiebreaker being the most recent visit date. I'm having a bit of trouble with the tiebreaker. If there are two records both ranked 1 based on # of visits for a certain MemberID, I want to set the IsFirst bit column to 1 on the record with the MAX(EncounterDate) for that MemberID. How should I go about doing this?

推荐答案

这可能对您有所帮助... 这是基于现有 emp 表的 Oracle 查询.我认为在发布问题时创建结构是个好主意.用更新等替换第一个选择...:更新你的表 SET your date = max_date (max_hire_date 在我的例子中) WHERE your_field IN (在我的例子中选择最大日期) AND rnk = 1 and rno = 1

This may help you... This is Oracle query based on existing emp table. I think it is a good idea to create structures when you posting a problem. Replace first select with update etc...: UPDATE your table SET your date = max_date (max_hire_date in my example) WHERE your_field IN (select max date as in my example) AND rnk = 1 and rno = 1

SELECT * FROM 
 (  
 SELECT deptno
      , ename
      , sal
      , RANK() OVER (PARTITION BY deptno ORDER BY sal desc) rnk 
      , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rno 
      , MAX(hiredate) OVER (PARTITION BY deptno ORDER BY deptno) max_hire_date
   FROM emp_test
  WHERE deptno = 20
 ORDER BY deptno
 )
 WHERE rnk = 1
   --AND rno = 1 -- or 2 or any other number...
/

SQL>

DEPTNO  ENAME   SAL    RNK  RNO HIREDATE    MAX_HIRE_DATE
-----------------------------------------------------------
 20     SCOTT   3000    1   1   1/28/2013   1/28/2013
 20     FORD    3000    1   2   12/3/1981   1/28/2013

这篇关于基于日期的 SQL 排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！