--Select * from product
--delete from  product

Insert into Product values 
('Blue Dream',
'Blue Dream, a sativa-dominant hybrid that originated in California, has achieved legendary status among West Coast strains. By crossing a Blueberry indica with the sativa Haze, Blue Dream balances full-body relaxation with gentle cerebral invigoration. Novice and veteran consumers alike enjoy the level effects of Blue Dream, which ease you gently into a calm euphoria.',
'Without a doubt, it’s the most common and illustrious strain found across the United States today.',
300,
null,
5,
1,
0,
'Marijuana',
getdate()

)

,(
'Bubba Kush',
'Bubba Kush is an indica strain that has gained notoriety in the U.S. and beyond for its heavy tranquilizing effects. Sweet hashish flavors with subtle notes of chocolate and coffee come through on the exhale as powerful relaxation takes over. Bubba’s stocky plant stature and bulky bud structure suggest Afghani descent, but its genetic origins aren’t certain: The breeder whom this strain was named after states that Bubba Kush emerged just after 1996, when an OG Kush hybrid pollinated an unknown indica strain obtained in New Orleans. The genetically ambiguous indica was simply called “Bubba.” Bubba Kush has flourished ever since.',
'It’s a crazy-popular tranquilizing Kush.',
250,
null,
5,
1,
0,
'Marijuana',
getdate()
)

,(
'Durban Poison',
'Durban Poison is special, in part, because it is one of the few remaining pure sativas out there. Being a pure sativa means more THC and very little CBD, so the body effects of an indica are not typically going to be present. However, that doesn’t take away from its potency.​',
'Durban Poison has South African roots and is well respected for its heady lift.',
150,
null,
5,
1,
0,
'Marijuana',
getdate()
)

,(
'Pineapple Express',
'Pineapple Express combines the potent and flavorful forces of its parent strains. The smell is likened to fresh apple and mango, with an overarching flavor profile mixing pineapple, pine, and cedar. This hard-hitting sativa-dominant hybrid provides a long-lasting energetic buzz perfect for productive afternoons and creative escapes.',
'There’s a reason this strain is a fan favorite. The taste is delightfully tropical with a pleasantly mellow buzz that will leave you giggling, and it’s one of the more versatile strains, wonderful for both day and nighttime use.',
350,
null,
5,
1,
0,
'Marijuana',
getdate()
)

,(
'Sunset Sherbet',
'Heir to the throne of its illustrious predecessor is Sunset Sherbet (sometimes spelled Sunset Sherbert), an indica-leaning hybrid with intoxicatingly potent effects. Sunset Sherbet inherits the genetic lineage of its powerhouse parent, whose parentage in turn includes the famed strains OG Kush, Cherry Pie, and Durban Poison. Crossed with an indica known as Pink Panties, Sunset Sherbet exhibits powerful full-body effects elevated by a jolt of cerebral energy. A complex aroma colors Sunset Sherbet with notes of skunky citrus, sweet berry, and a candy-like smell. Stress, tension, and sour moods melt away with the carefree mindset and physical relaxation that comes with this rich hybrid.',
'Blissful, tranquil, and euphoric at once, this strain lives up to its name and gives everything a golden glow.',
250,
null,
5,
1,
0,
'Marijuana',
getdate()
)


--Delete from ProductPictures where productID in (1,2)
--Delete from Product where productID in (1,2)
select * from ProductPictures
Insert into ProductPictures(ProductID,Path,CreatedOnDt)
values 
(1,'assets/images/product/Blue_Dream_1.jpg',getdate()),
(1,'assets/images/product/Blue_Dream_2.jpg',getdate()),
(2,'assets/images/product/Blue_Dream_1.jpg',getdate()),
(2,'assets/images/product/Blue_Dream_2.jpg',getdate()),
(3,'assets/images/product/babba_kush_1.jpg',getdate()),
(3,'assets/images/product/babba_kush_2.jpg',getdate()),
(4,'assets/images/product/Durban_poison_1.jpg',getdate()),
(4,'assets/images/product/Durban_poison_2.jpg',getdate()),
(5,'assets/images/product/Pineapple_Express_1.jpg',getdate()),
(5,'assets/images/product/Pineapple_Express_2.jpg',getdate()),
(6,'assets/images/product/Sunset_Sherbet_1.jpg',getdate()),
(6,'assets/images/product/Sunset_Sherbet_2.jpg',getdate())

delete from ProductPictures where ProductID = 6
Update Product set id = 1 where ID = 6



SET IDENTITY_INSERT Product ON
Insert into Product(
id,
name,
description,
shortDetails,
price,
salePrice,
stock,
new,
sale,
category,
CreatedOnDt
) values 

(1,
'Blue Dream',
'Blue Dream, a sativa-dominant hybrid that originated in California, has achieved legendary status among West Coast strains. By crossing a Blueberry indica with the sativa Haze, Blue Dream balances full-body relaxation with gentle cerebral invigoration. Novice and veteran consumers alike enjoy the level effects of Blue Dream, which ease you gently into a calm euphoria.',
'Without a doubt, it’s the most common and illustrious strain found across the United States today.',
300,
null,
5,
1,
0,
'Marijuana',
getdate()

)
SET IDENTITY_INSERT Product OFF

-- chech how many rows in the dataset
-- 查看数据集记录总数
SELECT count(*) FROM <table name>;

-- select random rows
-- 随机返回若干条记录
-- https://gist.github.com/404hub/11f55753ac23dc876a6e70461672b470
SELECT select_expression
FROM table_name
TABLESAMPLE SYSTEM/BERNOULLI ( argument [, ...] )
-- SYSTEM sampling method does block/page level sampling; BERNOULLI sampling method does a sequential scan.

-- caltulating percentiles
-- 计算各百分位数
-- https://gist.github.com/404hub/bdf81448887359c97f565418157ed1d2
-- fast method using window function
select max(buckets.colume_name), ntile as percentile
from(
    select colume_of_table, ntile(percentile_num_you_want)
    over (order by colume_of_table)
    from table_name
    ) as buckets
group by 2
order by 2

SELECT queryid, calls,
  trunc(total_time::numeric, 2) as total_time,
  trunc(mean_time::numeric, 2) as mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

DECLARE @roleName NVARCHAR(512) = 'L1 Support'
DECLARE @skillName NVARCHAR(512) = 'L0_Global_3Par SSC'
DECLARE @centerSecondaryRoleId INT
DECLARE @centerPguSkillId INT

SELECT TOP 1 @centerSecondaryRoleId = srm.Id
FROM dbo.SecondaryRoleMaster srm
WHERE srm.IsActive = 1
	AND srm.NAME = @roleName

SELECT TOP 1 @centerPguSkillId = vsm.CenterPguSkillId
FROM dbo.vw_SkillMaster vsm
WHERE vsm.CenterId = 1
	AND vsm.SkillName = @skillName

SELECT tbl.CenterSecondaryRoleId
	,srm.NAME AS SecondaryRole
	,vsm.PguName
	,vsm.SkillName
	,[dbo].[UFN_GetATPNameByATPId](tbl.AtpId) AS ActivityTypePortfolioName
	,qc.NAME AS Complexity
	,tbl.RequiredQuestionCount
	,tbl.AvailableQuestionCount
	,tbl.QuestionAvailability
FROM [dbo].[UDF_GetCsrXCpsXAtpXComplexityXRqcXAqc](@centerSecondaryRoleId, @centerPguSkillId) AS tbl
LEFT JOIN dbo.CenterXSecondaryRole csr ON tbl.CenterSecondaryRoleId = csr.Id
LEFT JOIN dbo.SecondaryRoleMaster srm ON csr.SecondaryRoleId = srm.Id
LEFT JOIN dbo.QuestionComplexity qc ON tbl.ComplexityId = qc.QuestionComplexityId
LEFT JOIN dbo.vw_SkillMaster vsm ON tbl.CenterPguSkillId = vsm.CenterPguSkillId
ORDER BY srm.NAME,vsm.PguName,vsm.SkillName,5

if object_id('tempdb.db.#tmp1', 'U') is not null drop table #tmp1
;

with cte as
(
select *
		,case when originzip is null and searchsource is not null then DestinationZip else originzip end as originzip2
		,case when cast(searchdate as date) between ('05-14-2019') and ('06-07-2019') then searchsource else null end as destinationzip_modified
from InformaticaTest.web.EchoDriveAvailableLoadsSource
)
,cte2 as
(
select *
		,case when originzip2 is null and destinationzip is not null then destinationzip else originzip2 end as originzip3
		,case when cast(searchdate as date) not between ('05-14-2019') and ('06-07-2019') then destinationzip else destinationzip_modified end as destinationzip_modified2
from cte
)
,cte3 as
(
select *
		,case when len(originzip3) = 4 then concat('0',originzip3) else originzip3 end as originzip4
from cte2
)
,cte4 as
(
select *
		,CASE WHEN OriginZip4 is not null THEN LEFT(RIGHT(CONCAT('000',LEFT(OriginZip4,5)),5),3) ELSE LEFT(REPLACE(OriginZip4,' ',''),3) END AS MarketZip_O
		,CASE WHEN destinationzip_modified2 is not null THEN LEFT(RIGHT(CONCAT('000',LEFT(destinationzip_modified2,5)),5),3) ELSE LEFT(REPLACE(destinationzip_modified2,' ',''),3) END AS MarketZip_D
from cte3
)
,cte5 as
(
select I.*
		,TLSalesRegionName as O_TLSalesRegionName
        ,TRI_Market as O_TRI_Market
from cte4 I
LEFT JOIN   TLPricing.rfp.[3digit_analysis] AS M ON I.MarketZip_O = M.[3digit]
LEFT JOIN   EchoOptimizer.Sales.ZipOfficeTLSalesRegion AS R ON I.MarketZip_O = R.ZipCode
LEFT JOIN   EchoOptimizer.Sales.OfficeTLSalesRegion AS SR ON R.OfficeTLSalesRegionId = SR.OfficeTLSalesRegionId
LEFT JOIN   EchoOptimizer.Sales.TLSalesRegion AS T ON SR.TLSalesRegionId = T.TLSalesRegionId
)
select I.*
		,TLSalesRegionName as D_TLSalesRegionName
        ,TRI_Market as D_TRI_Market
into #tmp1
from cte5 I
LEFT JOIN   TLPricing.rfp.[3digit_analysis] AS M ON I.MarketZip_D = M.[3digit]
LEFT JOIN   EchoOptimizer.Sales.ZipOfficeTLSalesRegion AS R ON I.MarketZip_D = R.ZipCode
LEFT JOIN   EchoOptimizer.Sales.OfficeTLSalesRegion AS SR ON R.OfficeTLSalesRegionId = SR.OfficeTLSalesRegionId
LEFT JOIN   EchoOptimizer.Sales.TLSalesRegion AS T ON SR.TLSalesRegionId = T.TLSalesRegionId
;

if object_id('tempdb.db.#tmp2', 'U') is not null drop table #tmp2

;
with acte as(
Select C.CarrierName
	,C.CarrierId
    ,C.OptimizerCarrierGuid
    ,ISNULL(U.FullName,'') AS CarrierOwner
    ,ISNULL(O.Office,'') AS CarrierOwnerOffice
    ,ISNULL(bu.Name,'') AS CarrierOwnerBU
FROM CarrierPortal.CarrierPortal.Carrier C
INNER JOIN  EchoOptimizer.dbo.tblCarrier CA ON C.OptimizerCarrierGuid = CA.CarrierGuid
LEFT JOIN   EchoOptimizer.[Ownership].TruckloadCarrierProfileOwnership AS TCPO ON TCPO.CarrierGuid = CA.CarrierGuid
LEFT JOIN   EchoOptimizer.Reference.TruckloadCarrierProfileOwnershipStatusType AS TCPOT ON TCPO.OwnershipStatusTypeId = TCPOT.OwnershipStatusTypeId
LEFT JOIN   EchoLogin2.dbo.[User] AS EL2 WITH(NoLock) ON TCPO.OwningRepGuid = EL2.UserID
LEFT JOIN   EchoOptimizer.dbo.tblUsers AS U ON EL2.Username = U.UserName
LEFT JOIN   EchoOptimizer.dbo.tblOffices as O on U.OfficeId = O.Officeid
LEFT JOIN   EchoOptimizer.dbo.tblBusinessUnits as BU on o.businessunitid = bu.businessunitid
)
,bcte as(
select cast(SearchDate as date) as SearchDay
		,acte.CarrierName
		,t.*
from #tmp1 t
left join acte on t.CarrierId = acte.CarrierId
)
select ROW_NUMBER() over(partition by acte.carrierid, searchday, origincity order by SearchDate asc) as row_a
		,SearchDay
		,SearchDate
		,acte.CarrierName
		,acte.CarrierId
		,acte.OptimizerCarrierGuid
		,OriginCity
		,OriginState
		,originzip4 as originzip
		,O_TRI_Market
		,case when DestinationCity in ('NONE', '') then null else DestinationCity end as DestinationCity
		,case when DestinationState in ('NONE', '') then null else DestinationState end as DestinationState
		,case when destinationzip_modified2 in ('NONE', '') then null else destinationzip_modified2 end as DestinationZip
		,D_TRI_Market
		,LoadedMilesMin
		,LoadedMilesMax
		,IsFilterSearch
		,NumberOfResults
		,CarrierOwner
		,CarrierOwnerOffice
		,CarrierOwnerBU
into #tmp2
from bcte
left join acte on bcte.carrierid = acte.CarrierId
;


if object_id('tempdb.db.#tmp3', 'U') is not null drop table #tmp3
select SearchDay
		,CarrierName
		,CarrierId
		,OptimizerCarrierGuid
		,OriginCity
		,OriginState
		,OriginZip
		,O_TRI_Market
		,DestinationCity
		,DestinationState
		,DestinationZip
		,D_TRI_Market
		,CarrierOwner
		,CarrierOwnerOffice
		,CarrierOwnerBU
into #tmp3
from #tmp2
group by SearchDay
		,CarrierName
		,CarrierId
		,OptimizerCarrierGuid
		,OriginCity
		,OriginState
		,OriginZip
		,DestinationCity
		,DestinationState
		,DestinationZip
		,CarrierOwner
		,CarrierOwnerOffice
		,CarrierOwnerBU
		,O_TRI_Market
		,D_TRI_Market
;

if object_id('tempdb.db.#tmp4', 'U') is not null drop table #tmp4
;
select *
		,(
		select cast(SQRT(((a.Latitude - b.Latitude) * (a.Latitude - b.Latitude)) + ((a.Longitude - b.Longitude) * (a.Longitude - b.Longitude))) * 67.5 as int)
        from EchoOptimizer.dbo.tblZipLonLat a, EchoOptimizer.dbo.tblZipLonLat b
        where a.ZipCode = OriginZip
        and b.ZipCode = DestinationZip
		) as distance
into #tmp4
from #tmp3
;

if object_id('tempdb.db.#tmp5', 'U') is not null drop table #tmp5
select case when SearchDay is null then '' else SearchDay end as SearchDay
		,case when CarrierName is null then '' else CarrierName end as CarrierName
		,case when CarrierId is null then '' else CarrierId end as CarrierId
		,case when OptimizerCarrierGuid is null then null else OptimizerCarrierGuid end as CarrierGuid
		,case when OriginCity is null then '' else OriginCity end as OriginCity
		,case when OriginState is null then '' else OriginState end as OriginState
		,case when OriginZip is null then '' else OriginZip end as OriginZip
		,case when DestinationCity is null then '' else DestinationCity end as DestinationCity
		,case when DestinationState is null then '' else DestinationState end as DestinationState
		,case when DestinationZip is null then '' else DestinationZip end as DestinationZip
		,case when CarrierOwner is null then '' else CarrierOwner end as CarrierOwner
		,case when CarrierOwnerOffice is null then '' else CarrierOwnerOffice end as CarrierOwnerOffice
		,case when CarrierOwnerBU is null then '' else CarrierOwnerBU end as CarrierOwnerBU 
		,case when O_TRI_Market is null then '' else O_TRI_Market end as O_TRI_Market
		,case when D_TRI_Market is null then '' else D_TRI_Market end as D_TRI_Market
		,case when distance is null then null else cast(distance as int) end as Distance
into #tmp5
from #tmp4
;

select *
from #tmp5

-- all info
SELECT *
FROM information_shcema.colunms
WHERE table_schema = 'public' AND table_name = '<table name>';

-- specific info
SELECT table_catalog, table_name, column_name, column_default, is_nullable, data_type
FROM information_schema.columns
WHERE table_schema = 'public'AND table_name = 'album';

SELECT password,
       public.crypt('abc', password) = password
FROM table
WHERE email = 'xxx@a.com';

UPDATE table
SET password=public.crypt('abc', public.gen_salt('bf', 8))
WHERE id = 'aaa';

    SELECT DISTINCT
        ffl.fmd_form_id,
        ffl.ffl_label_text,
        ffl.ffl_layout_id,
        fmd.fmd_form_name,
        so.so_id,
        TO_CHAR(fda.fda_field_value) AS field_value
    FROM
        sim.form_data fda
        INNER JOIN sim.form_field_layout ffl ON fda.ffl_layout_id = ffl.ffl_layout_id
        INNER JOIN sim.form_instance fin ON fda.fin_instance_id = fin.fin_instance_id
        INNER JOIN sim.form_definition fmd ON fin.fmd_form_id = fmd.fmd_form_id
        INNER JOIN sim.form_so_xref fsx ON fin.fin_instance_id = fsx.fin_instance_id
        INNER JOIN sim.service_order so ON fsx.so_id = so.so_id

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
;

-- 模式字段是我们这边自己做出来的逻辑, 是在sku表做的, 
-- 模式是和商品绑定的一个字段, 并不是用来描述广告主的.