我有一個名為 inputtable 的表，其中包含設備 ID（int）、遙測時間（時間戳）列，我想為每天早上 8 點到早上 8 點的行分配一個 row_number。以下是示例資料鏈接，
輸入表
| 設備_id | 遙測時間 |
|---|---|
| 2 | 2022-06-16 04:00:00 |
| 2 | 2022-06-16 04:30:00 |
| 2 | 2022-06-16 06:00:00 |
| 2 | 2022-06-16 06:30:00 |
| 2 | 2022-06-16 08:00:00 |
| 2 | 2022-06-16 08:30:00 |
| 2 | 2022-06-16 20:00:00 |
| 2 | 2022-06-16 23:30:00 |
| 2 | 2022-06-17 02:30:00 |
| 2 | 2022-06-17 03:00:00 |
| 2 | 2022-06-17 10:30:00 |
所需輸出
| 設備_id | 遙測時間 | 批 |
|---|---|---|
| 2 | 2022-06-16 04:00:00 | 1 |
| 2 | 2022-06-16 04:30:00 | 2 |
| 2 | 2022-06-16 06:00:00 | 3 |
| 2 | 2022-06-16 06:30:00 | 4 |
| 2 | 2022-06-16 08:00:00 | 1 |
| 2 | 2022-06-16 08:30:00 | 2 |
| 2 | 2022-06-16 20:00:00 | 3 |
| 2 | 2022-06-16 23:30:00 | 4 |
| 2 | 2022-06-17 02:30:00 | 5 |
| 2 | 2022-06-17 03:00:00 | 6 |
| 2 | 2022-06-17 10:30:00 | 1 |
我在下面的鏈接中添加了所需的輸出，表名是desiredtable https://www.db-fiddle.com/f/vhZbgTG8TcJ7SwqanoWEUL/4 請幫助我，我是 SQL 新手，
uj5u.com熱心網友回復：
將您的時間向后移動八小時以獲得正確的日期：
with date_shift as (
select equipment_id, telemetry_time,
(telemetry_time - interval '8 hours')::date as tdate
from inputtable
)
select equipment_id, telemetry_time,
row_number() over (partition by equipment_id, tdate
order by telemetry_time)
from date_shift;
db<>在這里擺弄
uj5u.com熱心網友回復：
select
*,
dense_rank() over (partition by date,
equipment_id
order by
telemetry_time) as batch
from
(
select
equipment_id,
(telemetry_time - interval '8 hour')::date as date,
telemetry_time
from
inputtable ) as t
order by
equipment_id,
telemetry_time
uj5u.com熱心網友回復：
試試這個查詢 注意：這個查詢不依賴于 Equipment_id。完全取決于日期和時間
select *,
rank() OVER (PARTITION by Shift_start_category::date, Shift_end_category::date ORDER BY telemetry_time asc)
from (
select equipment_id, telemetry_time,
case when(telemetry_time <= telemetry_time::date (' 8 hour')::interval) and (telemetry_time >= telemetry_time::date (' 1920 minute')::interval)
then telemetry_time::date (' -24 hour')::interval
when(telemetry_time >= telemetry_time::date (' 8 hour')::interval) and (telemetry_time <= telemetry_time::date (' 1920 minute')::interval)
then telemetry_time::date (' 24 hour')::interval
else telemetry_time::date (' 8 hour')::interval end Shift_start_category,
case when(telemetry_time >= telemetry_time::date (' 8 hour')::interval) and (telemetry_time <= telemetry_time::date (' 1920 minute')::interval)
then telemetry_time::date (' 24 hour')::interval
when(telemetry_time <= telemetry_time::date (' 8 hour')::interval) and (telemetry_time >= telemetry_time::date (' 1920 minute')::interval)
then telemetry_time::date (' 24 hour')::interval
else telemetry_time::date (' 8 hour')::interval end Shift_end_category
from public.inputtable) A
order by telemetry_time asc;
Shift_start_category 和 Shift_end_category 欄位僅用于顯示目的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/491940.html
標籤：sql 数据库 PostgreSQL