返回新单元格中整型的最后一个值

Question

我正在尝试根据Row_Number列的最后一个值将工作表中的特定行分配给代理。如果Row_Number以0-4结束，那么我希望它旁边的单元格状态为John。如果Row_Number以5-9结尾，那么我希望它旁边的单元格状态为Sam。

这是我希望看到的结果；

| Row_Number  | Agent_name  |
|-------------|-------------|
|      1      |     John    |
|      2      |     John    |
|      3      |     John    |
|      4      |     John    |
|      5      |     Sam     |
|      6      |     Sam     |
|      7      |     Sam     | 
|      8      |     Sam     | 

这是我到目前为止所拥有的；

SELECT ROW_NUMBER() OVER(ORDER BY id DESC) AS Row_number,
      CASE WHEN RIGHT("row_number",1) BETWEEN 0 AND 4 THEN John
           WHEN RIGHT("row_number",1) BETWEEN 5 AND 9 Then Sam
      END AS AGENT_NAME
FROM table
ORDER BY ROW_NUMBER ASC

似乎Presto可能不支持RIGHT函数，这就是我被卡住的地方。有人有什么建议吗？

Answer 1

您可以使用substr()：

SELECT Row_number, (CASE WHEN substr(reverse(Row_number), 1, 1) BETWEEN 0 AND 4 
                         THEN "John"
                         WHEN substr(reverse(Row_number), 1, 1) BETWEEN 5 AND 9 
                         THEN "Sam"
                    END) AS AGENT_NAME
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id DESC) AS Row_number, *
      FROM table
     ) t;