I finally had a few minutes of time thinking about the LLIL problem and looked into PostgreSQL for a solution.

Thank you for the PostgreSQL solution. I changed the ORDER BY clause to match the OP's requirement.

INSERT INTO llil_result (name, count)
    SELECT name, sum(count) AS total FROM llil_raw GROUP BY name ORDER
+ BY total desc, name;

[download]

Results from Linux installed on a USB 3.0 drive.

$ /usr/bin/postgres -D /var/lib/pgsql/data -h localhost

$ time psql -d test_db -f llil.sql
CREATE TABLE
COPY 3515200
COPY 3515200
COPY 3515200
CREATE TABLE
INSERT 0 10367603
COPY 10367603
DROP TABLE
DROP TABLE

real    0m52.508s
user    0m0.001s
sys     0m0.001s

[download]

Results from the DB residing in memory, /tmp/data location.

$ /usr/bin/postgres -D /tmp/data -h localhost

$ time psql -d test_db -f llil.sql 
CREATE TABLE
COPY 3515200
COPY 3515200
COPY 3515200
CREATE TABLE
INSERT 0 10367603
COPY 10367603
DROP TABLE
DROP TABLE

real    0m49.396s
user    0m0.001s
sys     0m0.002s

[download]

See also, SQLite solution. The results there were captured on Clear Linux. Here, on Ubuntu Linux 24.04. The Sort::Packed module is used for sorting the output.

$ perl -I ~/perl5/lib/perl5/ llilsql.pl --threads=1 big{1,2,3}.txt | c
+ksum
Tie::Hash::DBD SQLite database - start
fixed string length=12, threads=1, maps=32
get properties   :    41.352 secs
pack properties  :     5.897 secs
sort packed data :     1.164 secs
write stdout     :     3.602 secs
total time       :    52.017 secs
    count lines  : 10545600
    count unique : 10367603
2956888413 93308427

$ perl -I ~/perl5/lib/perl5/ llilsql.pl --threads=8 big{1,2,3}.txt | c
+ksum
Tie::Hash::DBD SQLite database - start
fixed string length=12, threads=8, maps=32
get properties   :     5.572 secs
pack properties  :     0.856 secs
sort packed data :     1.173 secs
write stdout     :     0.719 secs
total time       :     8.323 secs
    count lines  : 10545600
    count unique : 10367603
2956888413 93308427

$ perl -I ~/perl5/lib/perl5/ llilsql.pl --threads=16 --maps=64 big{1,2
+,3}.txt | cksum
Tie::Hash::DBD SQLite database - start
fixed string length=12, threads=16, maps=64
get properties   :     3.140 secs
pack properties  :     0.533 secs
sort packed data :     1.175 secs
write stdout     :     0.395 secs
total time       :     5.247 secs
    count lines  : 10545600
    count unique : 10367603
2956888413 93308427

[download]

In reply to Re^2: [OT] The Long List is Long resurrected by marioroy
in thread [OT] The Long List is Long resurrected by marioroy

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.