'''
sqlite3.exe

.open imagestags.db

# id,filename,tag1,tag1_score,tag2,tag2_score,tag3,tag3_score,tag4,tag4_score,tag5,tag5_score
create table imagestags(id integer, filename text, tag1 text, tag1_score REAL, tag2 text, tag2_score REAL, tag3 text, tag3_score REAL, tag4 text, tag4_score REAL, tag5 text, tag5_score REAL);


.databases

.tables

.separator ','

.import 'E:\2000_AI\0000_ImageCluster\1048576_results.csv' imagestags

select * from imagestags;

select * from sqlite_master where type="table";

select * from sqlite_master where type="table" and name="imagestags";

.schema imagestags 

 ALTER TABLE imagestags ADD COLUMN fullFeature Text; 

select tag1 || ',' || tag2  || ',' || tag3  || ',' || tag4  || ',' || tag5  from imagestags where id<5;

update imagestags 
set fullFeature = tag1 || ',' || tag2  || ',' || tag3  || ',' || tag4  || ',' || tag5;

select fullFeature  from imagestags where id<5;

select fullFeature  from imagestags where fullFeature like '%雪%';

select fullFeature  from imagestags where fullFeature like '%丛林%' and  fullFeature like '%雪景%';

select id as id,filename as filename,tag1 as tag1,tag1_score as tag1_score,tag2 as tag2,tag2_score as tag2_score,tag3 as tag3,tag3_score as tag3_score,tag4 as tag4,tag4_score as tag4_score,tag5 as tag5,tag5_score as tag5_score from imagestags  where  fullFeature like '%丛林%' and fullFeature like '%天空%' and fullFeature like '%雪景%';

select id as id,filename as filename,tag1 as tag1,tag1_score as tag1_score,tag2 as tag2,tag2_score as tag2_score,tag3 as tag3,tag3_score as tag3_score,tag4 as tag4,tag4_score as tag4_score,tag5 as tag5,tag5_score as tag5_score from imagestags  where  fullFeature like '%雪景%' and fullFeature like '%天空%' and fullFeature like '%丛林%' and fullFeature like '%山%';
select id as id,filename as filename,tag1 as tag1,tag1_score as tag1_score,tag2 as tag2,tag2_score as tag2_score,tag3 as tag3,tag3_score as tag3_score,tag4 as tag4,tag4_score as tag4_score,tag5 as tag5,tag5_score as tag5_score from imagestags  where  fullFeature like '%雪景%' and fullFeature like '%天空%' and fullFeature like '%丛林%' and fullFeature like '%山%' and fullFeature like '%日出日落%';

select ta.* ,tb.NO
from 
imagestags as ta,
(select tag1,tag2,tag3 row_number() over(partition by null order by null) as NO from imagestags group by tag1,tag2,tag3) as tb
where ta.tag1=tb.tag1 and ta.tag2=tb.tag2 and ta.tag3=tb.tag3

select ta.* ,tb.NO
from 
imagestags as ta,
(select tag1,tag2,tag3 row_number() over(partition by null order by null) as NO from imagestags group by tag1,tag2,tag3) as tb
where ta.tag1=tb.tag1 and ta.tag2=tb.tag2 and ta.tag3=tb.tag3;

select * from(
select * from
imagestags as ta
where ta.tag1 in (select tag1 from imagestags group by tag1 having count(*) >=2)  
and ta.tag2 in (select tag2 from imagestags group by tag2 having count(*) >=2)  
and ta.tag3 in (select tag3 from imagestags group by tag3 having count(*) >=2) ) order by tag1,tag2,tag3;


select * from(
select * from
imagestags as ta
where ta.tag1 in (select tag1 from imagestags group by tag1 having count(*) >=2)  
and ta.tag2 in (select tag2 from imagestags group by tag2 having count(*) >=2)  
and ta.tag3 in (select tag3 from imagestags group by tag3 having count(*) >=2)
 and ta.tag4 in (select tag4 from imagestags group by tag4 having count(*) >=2)
) order by tag1,tag2,tag3,tag4,tag5;

select * from(
select * from
imagestags as ta
where ta.tag1 in (select tag1 from imagestags group by tag1 having count(*) >=2)  
and ta.tag2 in (select tag2 from imagestags group by tag2 having count(*) >=2)  
and ta.tag3 in (select tag3 from imagestags group by tag3 having count(*) >=2)
 and ta.tag4 in (select tag4 from imagestags group by tag4 having count(*) >=2)
 and ta.tag5 in (select tag5 from imagestags group by tag5 having count(*) >=2)) order by tag1,tag2,tag3,tag4,tag5;


'''