红星的个人主页

USE cwbasehwjd;  -- 切换到目标数据库

 
 SELECT name FROM sys.tables
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

USE [cwbasehwjd];  
GO  
SELECT SCHEMA_NAME(t.schema_id) AS schema_name  
    ,t.name AS table_name  
FROM sys.tables t   
WHERE object_id NOT IN   
   (  
    SELECT parent_object_id   
    FROM sys.key_constraints   
    WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'  
    );  
GO  
  

SELECT  db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
,
    t.create_date AS CreatedDate,
 t.modify_date AS LastModifiedDate
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 0
GROUP BY 
    t.Name, s.Name, p.Rows,t.create_date,t.modify_date
ORDER BY 
    总共占用空间MB desc

SELECT 
    name AS TableName,
    create_date AS CreatedDate,
    modify_date AS LastModifiedDate
FROM 
    sys.tables
    order by LastModifiedDate asc

clickhouse-client --host clickhouse-server
clickhouse-client --host clickhouse-server --query "CREATE DATABASE IF NOT EXISTS tutorial"

CREATE TABLE tutorial.hits_v1
(
    `WatchID` UInt64,
    `JavaEnable` UInt8,
    `Title` String,
    `GoodEvent` Int16,
    `EventTime` DateTime,
    `EventDate` Date,
    `CounterID` UInt32,
    `ClientIP` UInt32,
    `ClientIP6` FixedString(16),
    `RegionID` UInt32,
    `UserID` UInt64,
    `CounterClass` Int8,
    `OS` UInt8,
    `UserAgent` UInt8,
    `URL` String,
    `Referer` String,
    `URLDomain` String,
    `RefererDomain` String,
    `Refresh` UInt8,
    `IsRobot` UInt8,
    `RefererCategories` Array(UInt16),
    `URLCategories` Array(UInt16),
    `URLRegions` Array(UInt32),
    `RefererRegions` Array(UInt32),
    `ResolutionWidth` UInt16,
    `ResolutionHeight` UInt16,
    `ResolutionDepth` UInt8,
    `FlashMajor` UInt8,
    `FlashMinor` UInt8,
    `FlashMinor2` String,
    `NetMajor` UInt8,
    `NetMinor` UInt8,
    `UserAgentMajor` UInt16,
    `UserAgentMinor` FixedString(2),
    `CookieEnable` UInt8,
    `JavascriptEnable` UInt8,
    `IsMobile` UInt8,
    `MobilePhone` UInt8,
    `MobilePhoneModel` String,
    `Params` String,
    `IPNetworkID` UInt32,
    `TraficSourceID` Int8,
    `SearchEngineID` UInt16,
    `SearchPhrase` String,
    `AdvEngineID` UInt8,
    `IsArtifical` UInt8,
    `WindowClientWidth` UInt16,
    `WindowClientHeight` UInt16,
    `ClientTimeZone` Int16,
    `ClientEventTime` DateTime,
    `SilverlightVersion1` UInt8,
    `SilverlightVersion2` UInt8,
    `SilverlightVersion3` UInt32,
    `SilverlightVersion4` UInt16,
    `PageCharset` String,
    `CodeVersion` UInt32,
    `IsLink` UInt8,
    `IsDownload` UInt8,
    `IsNotBounce` UInt8,
    `FUniqID` UInt64,
    `HID` UInt32,
    `IsOldCounter` UInt8,
    `IsEvent` UInt8,
    `IsParameter` UInt8,
    `DontCountHits` UInt8,
    `WithHash` UInt8,
    `HitColor` FixedString(1),
    `UTCEventTime` DateTime,
    `Age` UInt8,
    `Sex` UInt8,
    `Income` UInt8,
    `Interests` UInt16,
    `Robotness` UInt8,
    `GeneralInterests` Array(UInt16),
    `RemoteIP` UInt32,
    `RemoteIP6` FixedString(16),
    `WindowName` Int32,
    `OpenerName` Int32,
    `HistoryLength` Int16,
    `BrowserLanguage` FixedString(2),
    `BrowserCountry` FixedString(2),
    `SocialNetwork` String,
    `SocialAction` String,
    `HTTPError` UInt16,
    `SendTiming` Int32,
    `DNSTiming` Int32,
    `ConnectTiming` Int32,
    `ResponseStartTiming` Int32,
    `ResponseEndTiming` Int32,
    `FetchTiming` Int32,
    `RedirectTiming` Int32,
    `DOMInteractiveTiming` Int32,
    `DOMContentLoadedTiming` Int32,
    `DOMCompleteTiming` Int32,
    `LoadEventStartTiming` Int32,
    `LoadEventEndTiming` Int32,
    `NSToDOMContentLoadedTiming` Int32,
    `FirstPaintTiming` Int32,
    `RedirectCount` Int8,
    `SocialSourceNetworkID` UInt8,
    `SocialSourcePage` String,
    `ParamPrice` Int64,
    `ParamOrderID` String,
    `ParamCurrency` FixedString(3),
    `ParamCurrencyID` UInt16,
    `GoalsReached` Array(UInt32),
    `OpenstatServiceName` String,
    `OpenstatCampaignID` String,
    `OpenstatAdID` String,
    `OpenstatSourceID` String,
    `UTMSource` String,
    `UTMMedium` String,
    `UTMCampaign` String,
    `UTMContent` String,
    `UTMTerm` String,
    `FromTag` String,
    `HasGCLID` UInt8,
    `RefererHash` UInt64,
    `URLHash` UInt64,
    `CLID` UInt32,
    `YCLID` UInt64,
    `ShareService` String,
    `ShareURL` String,
    `ShareTitle` String,
    `ParsedParams` Nested(
        Key1 String,
        Key2 String,
        Key3 String,
        Key4 String,
        Key5 String,
        ValueDouble Float64),
    `IslandID` FixedString(16),
    `RequestNum` UInt32,
    `RequestTry` UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

CREATE TABLE tutorial.visits_v1
(
    `CounterID` UInt32,
    `StartDate` Date,
    `Sign` Int8,
    `IsNew` UInt8,
    `VisitID` UInt64,
    `UserID` UInt64,
    `StartTime` DateTime,
    `Duration` UInt32,
    `UTCStartTime` DateTime,
    `PageViews` Int32,
    `Hits` Int32,
    `IsBounce` UInt8,
    `Referer` String,
    `StartURL` String,
    `RefererDomain` String,
    `StartURLDomain` String,
    `EndURL` String,
    `LinkURL` String,
    `IsDownload` UInt8,
    `TraficSourceID` Int8,
    `SearchEngineID` UInt16,
    `SearchPhrase` String,
    `AdvEngineID` UInt8,
    `PlaceID` Int32,
    `RefererCategories` Array(UInt16),
    `URLCategories` Array(UInt16),
    `URLRegions` Array(UInt32),
    `RefererRegions` Array(UInt32),
    `IsYandex` UInt8,
    `GoalReachesDepth` Int32,
    `GoalReachesURL` Int32,
    `GoalReachesAny` Int32,
    `SocialSourceNetworkID` UInt8,
    `SocialSourcePage` String,
    `MobilePhoneModel` String,
    `ClientEventTime` DateTime,
    `RegionID` UInt32,
    `ClientIP` UInt32,
    `ClientIP6` FixedString(16),
    `RemoteIP` UInt32,
    `RemoteIP6` FixedString(16),
    `IPNetworkID` UInt32,
    `SilverlightVersion3` UInt32,
    `CodeVersion` UInt32,
    `ResolutionWidth` UInt16,
    `ResolutionHeight` UInt16,
    `UserAgentMajor` UInt16,
    `UserAgentMinor` UInt16,
    `WindowClientWidth` UInt16,
    `WindowClientHeight` UInt16,
    `SilverlightVersion2` UInt8,
    `SilverlightVersion4` UInt16,
    `FlashVersion3` UInt16,
    `FlashVersion4` UInt16,
    `ClientTimeZone` Int16,
    `OS` UInt8,
    `UserAgent` UInt8,
    `ResolutionDepth` UInt8,
    `FlashMajor` UInt8,
    `FlashMinor` UInt8,
    `NetMajor` UInt8,
    `NetMinor` UInt8,
    `MobilePhone` UInt8,
    `SilverlightVersion1` UInt8,
    `Age` UInt8,
    `Sex` UInt8,
    `Income` UInt8,
    `JavaEnable` UInt8,
    `CookieEnable` UInt8,
    `JavascriptEnable` UInt8,
    `IsMobile` UInt8,
    `BrowserLanguage` UInt16,
    `BrowserCountry` UInt16,
    `Interests` UInt16,
    `Robotness` UInt8,
    `GeneralInterests` Array(UInt16),
    `Params` Array(String),
    `Goals` Nested(
        ID UInt32,
        Serial UInt32,
        EventTime DateTime,
        Price Int64,
        OrderID String,
        CurrencyID UInt32),
    `WatchIDs` Array(UInt64),
    `ParamSumPrice` Int64,
    `ParamCurrency` FixedString(3),
    `ParamCurrencyID` UInt16,
    `ClickLogID` UInt64,
    `ClickEventID` Int32,
    `ClickGoodEvent` Int32,
    `ClickEventTime` DateTime,
    `ClickPriorityID` Int32,
    `ClickPhraseID` Int32,
    `ClickPageID` Int32,
    `ClickPlaceID` Int32,
    `ClickTypeID` Int32,
    `ClickResourceID` Int32,
    `ClickCost` UInt32,
    `ClickClientIP` UInt32,
    `ClickDomainID` UInt32,
    `ClickURL` String,
    `ClickAttempt` UInt8,
    `ClickOrderID` UInt32,
    `ClickBannerID` UInt32,
    `ClickMarketCategoryID` UInt32,
    `ClickMarketPP` UInt32,
    `ClickMarketCategoryName` String,
    `ClickMarketPPName` String,
    `ClickAWAPSCampaignName` String,
    `ClickPageName` String,
    `ClickTargetType` UInt16,
    `ClickTargetPhraseID` UInt64,
    `ClickContextType` UInt8,
    `ClickSelectType` Int8,
    `ClickOptions` String,
    `ClickGroupBannerID` Int32,
    `OpenstatServiceName` String,
    `OpenstatCampaignID` String,
    `OpenstatAdID` String,
    `OpenstatSourceID` String,
    `UTMSource` String,
    `UTMMedium` String,
    `UTMCampaign` String,
    `UTMContent` String,
    `UTMTerm` String,
    `FromTag` String,
    `HasGCLID` UInt8,
    `FirstVisit` DateTime,
    `PredLastVisit` Date,
    `LastVisit` Date,
    `TotalVisits` UInt32,
    `TraficSource` Nested(
        ID Int8,
        SearchEngineID UInt16,
        AdvEngineID UInt8,
        PlaceID UInt16,
        SocialSourceNetworkID UInt8,
        Domain String,
        SearchPhrase String,
        SocialSourcePage String),
    `Attendance` FixedString(16),
    `CLID` UInt32,
    `YCLID` UInt64,
    `NormalizedRefererHash` UInt64,
    `SearchPhraseHash` UInt64,
    `RefererDomainHash` UInt64,
    `NormalizedStartURLHash` UInt64,
    `StartURLDomainHash` UInt64,
    `NormalizedEndURLHash` UInt64,
    `TopLevelDomain` UInt64,
    `URLScheme` UInt64,
    `OpenstatServiceNameHash` UInt64,
    `OpenstatCampaignIDHash` UInt64,
    `OpenstatAdIDHash` UInt64,
    `OpenstatSourceIDHash` UInt64,
    `UTMSourceHash` UInt64,
    `UTMMediumHash` UInt64,
    `UTMCampaignHash` UInt64,
    `UTMContentHash` UInt64,
    `UTMTermHash` UInt64,
    `FromHash` UInt64,
    `WebVisorEnabled` UInt8,
    `WebVisorActivity` UInt32,
    `ParsedParams` Nested(
        Key1 String,
        Key2 String,
        Key3 String,
        Key4 String,
        Key5 String,
        ValueDouble Float64),
    `Market` Nested(
        Type UInt8,
        GoalID UInt32,
        OrderID String,
        OrderPrice Int64,
        PP UInt32,
        DirectPlaceID UInt32,
        DirectOrderID UInt32,
        DirectBannerID UInt32,
        GoodID String,
        GoodName String,
        GoodQuantity Int32,
        GoodPrice Int64),
    `IslandID` FixedString(16)
)
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)

update   baseinfo_tb_b_carclass m INNER JOIN (
select brand_id,class_id ,ROW_NUMBER() OVER (PARTITION BY BRAND_ID ORDER BY class_id,class_name) AS sub_index
from baseinfo_tb_b_carclass m1 
GROUP BY brand_id ,class_id
order by brand_id,sub_index
) mi 
on mi.class_id=m.class_id  
set m.show_order=mi.sub_index;




update baseinfo_tb_b_carclass set show_order=10;

update   baseinfo_tb_b_carclass m INNER JOIN (
select brand_id,class_id ,ROW_NUMBER() OVER (PARTITION BY BRAND_ID ORDER BY class_id,class_name) AS sub_index
from baseinfo_tb_b_carclass m1 
GROUP BY brand_id ,class_id
order by brand_id,sub_index
) mi 
on mi.class_id=m.class_id  
set m.show_order=mi.sub_index;

select m1.id,m1.dim,m1.dim_real,m1.cal_order,m1.show_order, m1.cal_order + mi.sub_index * POW(100, mi.pow),mi.pow,mi.sub_index,POW(100, mi.pow)
from  cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code,  sub_index,pow
from cem_metrics m2
where m2.dim like "%_$" and m2.sub_code is not null and m2.sub_code !='0'  ) mi
on  mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
 where m1.variable=1
and m1.sub_code is not null 
and m1.sub_code !='0'
and m1.store = 'nm00012164'
and m1.month=4
and m1.dim like 'REV_YS_FIN_NC_FY_$%';

select m1.id,m1.dim,m1.dim_real,m1.cal_order,m1.show_order, m1.cal_order + mi.sub_index * POW(100, mi.pow),mi.pow,mi.sub_index,POW(100, mi.pow)
from  cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code,  sub_index,pow
from cem_metrics m2
where m2.dim like "%_$" and m2.sub_code is not null and m2.sub_code !='0'  ) mi
on  mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
 where m1.variable=1
and m1.sub_code is not null 
and m1.sub_code !='0'
and m1.store = 'nm00012164'
and m1.month=6
and m1.dim like 'REV_YS_FIN_NC_FY_%';



 update cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code,  sub_index,pow
from cem_metrics m2
where m2.dim like "%_$" and m2.sub_code is not null and m2.sub_code !='0'  ) mi
on  mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
set m1.show_order=m1.cal_order + mi.sub_index * POW(100, 2)
where m1.variable=1
and m1.sub_code is not null 
and m1.sub_code !='0'
and m1.store = 'nm00012164'
and m1.month=4
and m1.dim like 'BAS_NC_INVCNT_DEC_SUB%';


update cem_metrics m
INNER JOIN cem_dim d 
on d.code = m.dim
set m.name = d.name,m.cal_order=d.cal_order,m.show_order=d.cal_order,m.dim_parent = d.parent_id
,m.dim_root=d.root_id,m.pow=d.pow,m.variable=d.variable,m.val_type=d.val_type ;


update   cem_metrics m INNER JOIN (select store , dim, sub_code,ROW_NUMBER() OVER (PARTITION BY store,dim ORDER BY store,dim) AS sub_index
from cem_metrics m1 
where   dim like "%_$" and sub_code is not null and sub_code !='0'
GROUP BY store,dim, sub_code
order by store,dim, sub_code) mi 
on mi.store=m.store and mi.dim=m.dim and mi.sub_code = m.sub_code
set m.sub_index=mi.sub_index;

update  cem_metrics m 
INNER JOIN cem_dim dim on dim.code = m.dim and dim.has_sub_detail=1 and dim.sub_detail_type=1
inner join baseinfo_tb_b_carclass c on c.CLASS_CODE =m.sub_code
set m.sub_index =c.show_order;


update cem_metrics set show_order=cal_order;
 

 select m1.id,m1.dim,m1.dim_root,m1.cal_order,m1.pow,mi.cal_order,mi.show_order,CONVERT(mi.show_order + m1.sub_index * POW(100, m1.pow),char)
from 	cem_metrics m1 INNER JOIN (select distinct store , dim,dim_root, sub_code,  sub_index,pow,cal_order,show_order
from cem_metrics m2
where m2.dim like "%_$"  and m2.dim_root=m2.dim  and m2.sub_code is not null and m2.sub_code !='0'  ) mi
on  mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
where m1.variable=1
and m1.dim != m1.dim_root
and m1.dim ='REV_NC_SALE_RETAIL_$_AVG'
and m1.store='nm00012164'
and m1.`month`=6;

 update cem_metrics    
set show_order=cal_order + sub_index * POW(100, pow)
where  dim like "%_$" and dim_root=dim  and  sub_code is not null and  sub_code !='0'  
 and variable=1;
 

 update cem_metrics m1 INNER JOIN (select distinct store , dim, sub_code,  sub_index,pow,cal_order,show_order
from cem_metrics m2
where m2.dim like "%_$"  and m2.dim_root=m2.dim  and m2.sub_code is not null and m2.sub_code !='0'  ) mi
on  mi.store=m1.store and mi.dim=m1.dim_root and mi.sub_code = m1.sub_code
set m1.show_order=mi.show_order + m1.sub_index * POW(100, m1.pow)
where m1.variable=1
and m1.dim != m1.dim_root;




update cem_metrics m
set m.show_name = m.name ;


update cem_metrics m
set m.show_name = m.sub_name
where   m.dim like "%_$" and m.sub_code !='0' and  m.sub_code is not null;

 update cem_metrics set dim_real = dim   where variable=0;
 update cem_metrics set dim_real =replace(dim,"$",sub_code)  where variable=1;
  update cem_metrics set dim_real_parent = dim_parent   where variable=0;
 update cem_metrics set dim_real_parent =replace(dim_parent,"$",sub_code)  where variable=1;

set oracle_sid=DATABASE NAME
sqlplus /nolog
conn sys/sys as sysdba
shutdown abort
startup