211214:獲取一段時(shí)間有幾個(gè)星期一-postgreSQL按天分組-COALESCE函數(shù)-存JSON格式字段取值

一. Java獲取一段時(shí)間有幾個(gè)星期一

import java.text.DateFormat;
import java.util.Calendar;
/**
       * 給定時(shí)間段和星期幾，計(jì)算該時(shí)間段內(nèi)共有多少個(gè)給定的星期幾
       * @param start 開(kāi)始時(shí)間,格式y(tǒng)yyy-MM-dd
       * @param end 結(jié)束時(shí)間，格式y(tǒng)yyy-MM-dd
       * @param a 星期幾，從星期一到星期天，分別用數(shù)字1-7表示
       * @return 星期幾統(tǒng)計(jì)數(shù)
       */
private long weekend(String start,String end,int a){
    DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    long sunDay = 0;//計(jì)數(shù)
    try{
        Calendar startDate = Calendar.getInstance(); //開(kāi)始時(shí)間
        startDate.setTime(format.parse(start));
        Calendar endDate = Calendar.getInstance();//結(jié)束時(shí)間
        endDate.setTime(format.parse(end));

        int SW = startDate.get(Calendar.DAY_OF_WEEK)-1;//開(kāi)始日期是星期幾
        int EW = endDate.get(Calendar.DAY_OF_WEEK)-1;//結(jié)束日期是星期幾

        long diff = endDate.getTimeInMillis()-startDate.getTimeInMillis();   
        long days = diff / (1000 * 60 * 60 * 24);//給定時(shí)間段內(nèi)一共有多少天
        long w = Math.round(Math.ceil(((days+SW+(7-EW))/7.0)));//給定時(shí)間內(nèi)，共有多少個(gè)星期
        sunDay = w;//總的星期幾統(tǒng)計(jì)數(shù)
        if(a<SW)//給定的星期幾小于起始日期的星期幾，需要減少一天
            sunDay--;
        if(a>EW)//給定的星期幾大于結(jié)束日期的星期幾，需要減少一天
            sunDay--;
    }catch(Exception se){
        se.printStackTrace();
    }
    return sunDay;
}

二. postgreSQL按天分組

1. 按天分組

SELECT date_trunc('day', hpdr.test_time) AS "day" , count(*) AS "sum_count"
FROM health_patient_device_result hpdr
WHERE hpdr.test_time > now() - interval '3 months' 
GROUP BY 1 
ORDER BY 1;

2. 按周分組

SELECT date_trunc('week', loggedin) AS "Week" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '3 months' 
GROUP BY 1
ORDER BY 1;

3. 按月分組

![004](E:\ruanghDoc\LearningClip\學(xué)習(xí)筆記\新能康-復(fù)盤(pán)\211214-Java獲取一段時(shí)間有幾個(gè)星期一\004.png)SELECT date_trunc('month', loggedin) AS "Month" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '1 year' 
GROUP BY 1
ORDER BY 1;

4. 顯示近10個(gè)月年月

SELECT
    to_char( date_trunc( 'month', YearMonth ), 'YYYY-MM' ) 
FROM
    generate_series ( CURRENT_DATE - INTERVAL '10 month', CURRENT_DATE, '1 month' :: INTERVAL ) YearMonth;

三. PostgreSQL：COALESCE函數(shù)

COALESCE函數(shù)是返回參數(shù)中的第一個(gè)非null的值，它要求參數(shù)中至少有一個(gè)是非null的，如果參數(shù)都是null會(huì)報(bào)錯(cuò)。

![](E:\ruanghDoc\LearningClip\學(xué)習(xí)筆記\新能康-復(fù)盤(pán)\211214-Java獲取一段時(shí)間有幾個(gè)星期一\005.png)select COALESCE(null,null); -- 報(bào)錯(cuò)
select COALESCE(null,null,now(),''); -- 結(jié)果會(huì)得到當(dāng)前的時(shí)間
select COALESCE(null,null,'',now()); -- 結(jié)果會(huì)得到''

-- 可以和其他函數(shù)配合來(lái)實(shí)現(xiàn)一些復(fù)雜點(diǎn)的功能：查詢學(xué)生姓名，如果學(xué)生名字為null或''則顯示“姓名為空”
select case when coalesce(name,'') = '' then '姓名為空' else name end from student;

四. postgresql中將時(shí)間減1秒

select '2014-04-14 12:17:55.772'::timestamp - interval '1 seconds';

五. 存JSON格式字段取值

1. 取值

-- "->>" 通過(guò)鍵獲得 JSON 對(duì)象域 結(jié)果為text 
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;

-- "->" 通過(guò)鍵獲得 JSON 對(duì)象域 結(jié)果為json對(duì)象
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' as nickname;

2. 包含和存在

-- nickname 為 gs 的用戶 這里使用 ->> 查出的數(shù)據(jù)為text，所以匹配項(xiàng)也應(yīng)該是text
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' = 'gs';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';
 
-- 使用 -> 查詢，會(huì)拋出錯(cuò)誤，這里無(wú)論匹配項(xiàng)是text類型的 'gs'  還是 json 類型的 '"gs"'::json都會(huì)拋出異常，json 類型不支持 等號(hào)（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"';
ERROR:  operator does not exist: json = unknown
 
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"'::json;
ERROR:  operator does not exist: json = json
 
-- jsonb 格式是可以查詢成功的，這里使用 -> 查出的數(shù)據(jù)為json 對(duì)象，所以匹配項(xiàng)也應(yīng)該是json 對(duì)象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';

3. #> 和 #>> 操作符(數(shù)組)

使用 #>> 查出的數(shù)據(jù)為text 使用 #> 查出的數(shù)據(jù)為json 對(duì)象

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;
   tag
----------
 "python"
 
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' as tag;
  tag
--------
 python
 
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>'{tags,0}' = '"python"';
 ?column?
----------
 t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>>'{tags,0}' = 'python';
 ?column?
----------
 t
 
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' = 'python';
 ?column?
----------
 t
-- 會(huì)拋出錯(cuò)誤，這里無(wú)論匹配項(xiàng)是text類型的 'python'  還是 json 類型的 '"python"'::json都會(huì)拋出異常，json 類型不支持 等號(hào)（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' = '"python"';
ERROR:  operator does not exist: json = unknown

4. jsonb 數(shù)據(jù)查詢（不適用于json）

@>操作符

-- nickname 為 nickname 的用戶
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;
 
-- 等同于以下查詢
-- 這里使用 -> 查出的數(shù)據(jù)為json 對(duì)象，所以匹配項(xiàng)也應(yīng)該是json 對(duì)象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';
 
-- 查詢有 python 和 golang 標(biāo)簽的數(shù)據(jù)
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"tags": ["python", "golang"]}';
 ?column?
----------
 t

5. ?操作符、?|操作符和?&操作符 

-- 查詢有 avatar 屬性的用戶
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar';
-- 查詢有 avatar 屬性 并且avatar 數(shù)據(jù)不為空的數(shù)據(jù)
select '{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}'::jsonb->>'avatar' is not null;
 
-- 查詢 有 avatar 或 tags 的數(shù)據(jù)
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?| array['avatar', 'tags'];
 ?column?
----------
 t
 
-- 查詢 既有 avatar 又有 tags 的用戶
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?& array['avatar', 'tags'];
 ?column?
----------
 f
 
 -- 查詢 tags 中包含 python 標(biāo)簽的數(shù)據(jù)
 select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'tags' ? 'python';
 ?column?
----------
 t
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容