sql语句

SELECT
    fmr.fund_id,
    fmr.statistic_date,
    fmr.benchmark,
    fmr.total_return
FROM
    fund_month_return fmr
JOIN (
    SELECT
        fund_id,
        max(statistic_date) AS msd
    FROM
        fund_month_return
    GROUP BY
        fund_id
) temp ON temp.fund_id = fmr.fund_id
AND temp.msd = fmr.statistic_date
WHERE
    fmr.fund_id IN (
        "JR000001",
        "JGH006",
        "JGH007",
        "S00002"
    )

---

SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn
  FROM tips t
)
WHERE rn < 3
ORDER BY day, rn;
#ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn
#同下，都是用来计算rn的。

or

In [35]: (tips.assign(rn=tips.sort_values(['total_bill'], ascending=False)
   ....:                     .groupby(['day'])
   ....:                     .cumcount() + 1)
   ....:      .query('rn < 3')
   ....:      .sort_values(['day','rn'])
   ....: )
   ....: 
#rn指的是'day'的排名数，选出'day'排名前三的行
Out[35]: 
     total_bill    tip     sex smoker   day    time  size  rn
95        40.17   4.73    Male    Yes   Fri  Dinner     4   1
90        28.97   3.00    Male    Yes   Fri  Dinner     2   2
170       50.81  10.00    Male    Yes   Sat  Dinner     3   1
212       48.33   9.00    Male     No   Sat  Dinner     4   2
156       48.17   5.00    Male     No   Sun  Dinner     6   1
182       45.35   3.50    Male    Yes   Sun  Dinner     3   2
197       43.11   5.00  Female    Yes  Thur   Lunch     4   1
142       41.19   5.00    Male     No  Thur   Lunch     5   2